Interesting SQL Server 2008 problem
-
I have to servers that are just about identical in terms of hardware and performance. One dev and one production, both running sql server 2k8. Both of them afaik are configured the same, dev just has an older smaller copy of the production database, its only smaller because of its age. One of its tables has 40M rows, the same table in prodcution has 60M. I needed to make a query faster so i did soem research and since this table (i dotn want to hear any complaints about this next bit) doesnt have and really cant have a PK i was looking at making a non clustered index, or at least changing the one it has. There are so many inserts, updates and deletes i imagine a clustered index would get destroyed in a few hours, or cripple performance. There is currently an index on Id, its not unique just a way of grouping records that go together. I read about the INCLUDE keyword that was new in server 2k8 and how it should speed up selects if i included the columns in the where and select clause in the index so it wouldnt have to do the RID or Bookmark lookup(its where 99% of my time was bieng taken). So i changed the index in dev, added every column in the table(since we always use every column in the table any time we go to it, and there's only 10 or so). so the index was CREATE INDEX uix_name id INCLUDE (col1, col2, col3, ...). The SELECT's performance skyrocketed, but since i now have an index that looks at every column, updates and deletes should run slower..much slower. They didnt, they ran even faster. This is the part i dont understand at all. I put the same index on the production server, and the select ran faster, but updates, deletes, and inserts all got much much slower(this i would have expected). for a comparison i uploaded 1M records into the table, on dev it took 2:48 after index, it wouldnt finish before(through our wep app not through the db), in production it took 4:21 ish(no index changes) and over 6 minutes after i changed the index. Can anyone please offer any insight as to why my dev server got faster and the prodcution server did what we thought it should and got slower? I have no idea where to even start looking. Thanks
Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.
-
I have to servers that are just about identical in terms of hardware and performance. One dev and one production, both running sql server 2k8. Both of them afaik are configured the same, dev just has an older smaller copy of the production database, its only smaller because of its age. One of its tables has 40M rows, the same table in prodcution has 60M. I needed to make a query faster so i did soem research and since this table (i dotn want to hear any complaints about this next bit) doesnt have and really cant have a PK i was looking at making a non clustered index, or at least changing the one it has. There are so many inserts, updates and deletes i imagine a clustered index would get destroyed in a few hours, or cripple performance. There is currently an index on Id, its not unique just a way of grouping records that go together. I read about the INCLUDE keyword that was new in server 2k8 and how it should speed up selects if i included the columns in the where and select clause in the index so it wouldnt have to do the RID or Bookmark lookup(its where 99% of my time was bieng taken). So i changed the index in dev, added every column in the table(since we always use every column in the table any time we go to it, and there's only 10 or so). so the index was CREATE INDEX uix_name id INCLUDE (col1, col2, col3, ...). The SELECT's performance skyrocketed, but since i now have an index that looks at every column, updates and deletes should run slower..much slower. They didnt, they ran even faster. This is the part i dont understand at all. I put the same index on the production server, and the select ran faster, but updates, deletes, and inserts all got much much slower(this i would have expected). for a comparison i uploaded 1M records into the table, on dev it took 2:48 after index, it wouldnt finish before(through our wep app not through the db), in production it took 4:21 ish(no index changes) and over 6 minutes after i changed the index. Can anyone please offer any insight as to why my dev server got faster and the prodcution server did what we thought it should and got slower? I have no idea where to even start looking. Thanks
Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.
Dev servers, due to their close relationship with, well developers, tend to be better behaved than production servers and they quickly learn the consequences of disruptive behavior (the 3 fingered salute is not unknown to a dev server). Surely you have been in the position where a user cannot get something working until you go stand beside them, then the dammed thing behaves itself. The same applies to servers. Also SQL likes to throw in the odd bizarre behavior occasionally, one of the favourites is to take 10-100 times as long processing a query from the application than it does in SSMS. To date I have not found a solution to this odd behaviour, however I have found it beneficial to NOT discuss these aberrations with my manager! Good Luck...
Never underestimate the power of human stupidity RAH
-
Dev servers, due to their close relationship with, well developers, tend to be better behaved than production servers and they quickly learn the consequences of disruptive behavior (the 3 fingered salute is not unknown to a dev server). Surely you have been in the position where a user cannot get something working until you go stand beside them, then the dammed thing behaves itself. The same applies to servers. Also SQL likes to throw in the odd bizarre behavior occasionally, one of the favourites is to take 10-100 times as long processing a query from the application than it does in SSMS. To date I have not found a solution to this odd behaviour, however I have found it beneficial to NOT discuss these aberrations with my manager! Good Luck...
Never underestimate the power of human stupidity RAH
I did, finally, notice 2 differences in the configuration of the databases. The compatibility mode and Page_Verify settings were different. Dev was set to 80 (sql server 2k) and torn page, prod was set to 100(server 2k8) and checksum. I was under the impression that new fancy features wouldnt work on a database unless the compat mode was set to the correct version. I checked the queries(update and select) in SSMS and compared execution plans and they looked identical except for the time taken. I forgot to mention that we use Bulk Insert to insert the million rows, dont know if that would make a difference or not.
Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.
-
I did, finally, notice 2 differences in the configuration of the databases. The compatibility mode and Page_Verify settings were different. Dev was set to 80 (sql server 2k) and torn page, prod was set to 100(server 2k8) and checksum. I was under the impression that new fancy features wouldnt work on a database unless the compat mode was set to the correct version. I checked the queries(update and select) in SSMS and compared execution plans and they looked identical except for the time taken. I forgot to mention that we use Bulk Insert to insert the million rows, dont know if that would make a difference or not.
Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.
SQL 2008 does some strange things on bulk insert. I have had to rewrite several processes that worked just fine on 2K that do not work on 2008. Import wizard in SSMS does not import things that Enterprise Manager did fine.
-
SQL 2008 does some strange things on bulk insert. I have had to rewrite several processes that worked just fine on 2K that do not work on 2008. Import wizard in SSMS does not import things that Enterprise Manager did fine.
I have noticed a few cases where the import wizard had turned into a complete pile, i really miss Enterprise Manager, Object Explorer annoyes the crap outa me. I cant figure out why an index change that shouldnt be supported on the database made everything faster, and screwed up everything but reading on the database where it should have been supported.(actually the last bit is what i expected to happen, so i understand that just fine).
Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.