February 28, 2014 at 10:09 am
Hi all,
Client site has a 2K DB with several tables each containing millions of rows, none of them have indices (yeah....I'm serious), in violation of at least one of Codd's Laws (about not having data needlessly repeated) and is probably suffering sub-optimal design throughout, ie a 'year' field that's Varchar 8000.
To my understanding, the application queries some of the tables, performs a further join against an external dataset, now the DB started life with just a few hundred rows per table, but now...you can imagine how whiny the users are getting!!
🙂
My plan is to
1) Get an idea of the Sp's/UDF's being run by the app;
1.5) Possibly run a Profiler (server-side) Trace for long-running queries;
2) Run the output of both through the Index Tuning Wizard;
3) Build indices as appropriate.
Anything else i can do? Ie the grossly exaggerated column widths, should these be rationalised, and if so, how?
Thanks,
Jake.
February 28, 2014 at 10:28 am
Jake Shelton (2/28/2014)
Hi all,Client site has a 2K DB with several tables each containing millions of rows, none of them have indices (yeah....I'm serious), in violation of at least one of Codd's Laws (about not having data needlessly repeated) and is probably suffering sub-optimal design throughout, ie a 'year' field that's Varchar 8000.
To my understanding, the application queries some of the tables, performs a further join against an external dataset, now the DB started life with just a few hundred rows per table, but now...you can imagine how whiny the users are getting!!
🙂
My plan is to
1) Get an idea of the Sp's/UDF's being run by the app;
1.5) Possibly run a Profiler (server-side) Trace for long-running queries;
2) Run the output of both through the Index Tuning Wizard;
3) Build indices as appropriate.
Anything else i can do? Ie the grossly exaggerated column widths, should these be rationalised, and if so, how?
Thanks,
Jake.
Take anything the DTA suggests with a grain of salt. It can and will actually provide suggestions what will be worse. I would recommend digging through the Stairways series on indexing. http://www.sqlservercentral.com/stairway/72399/[/url]
Definitely finding long running queries is a good place to start. This sounds like it is going to be a lengthy uphill battle.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 2, 2014 at 8:04 am
Thank you Sean - again, is it worth changing the table structures? Also, will we need to point the sprocs to the new indices?
BTW - we are porting these DB's in due course to an SS2K8 box, so hopefully we'll also add some DMV's at the other end.
March 2, 2014 at 9:35 am
Jake Shelton (3/2/2014)
Thank you Sean - again, is it worth changing the table structures? Also, will we need to point the sprocs to the new indices?BTW - we are porting these DB's in due course to an SS2K8 box, so hopefully we'll also add some DMV's at the other end.
Although you can use table "hints" to "point" procs at indexes, it usually doesn't help. In most cases, if the procs don't use available indexes, it's because they can't use them effectively because of the condition of the underlying code or the datatypes in a proc.
To be honest, my first step would be to forget about table, proc, and index changes for now and get the database moved to the 2K8 box where you can take advantage of modern features to make the database and the related app better.
Once that's done, then...
Yes, it's definitely worth changing the table structures and the datatypes if performance and data integrity is to ever be achieved. Do understand that if the database is in that bad of a condition, then the front-end code is probably equally deplorable or worse and that even changing a DATETIME column from VARCHAR(8000) to a DATETIME datatype could break a whole lot of "managed" code. That shouldn't stop you and your team but it does need to be planned for.
I agree with what Sean said about DTA. It's a great way to get a quick list of things that you need to look at but (IMHO) the indexes that it suggests to be created are usually riddled with full covering indexes that contain many INCLUDE columns. The important thing to remember is that all non-clustered indexes are nothing more than a sorted duplication of data. Multiple wide covering indexes can easily double or triple (typical, it can be much worse) the size of a database, make inserts, updates, and deletes horribly slow (all the indexes have to be updated for inserts and deletes) to the point of getting insurmountable timeouts and increases in deadlocks.
Because of the duplication of data, the backups may no longer fit on their disks, tape backups will take much longer, emergency restores will take much longer, making copies of the database for development may become impossible because they'll no longer fit on the Dev boxes, and nightly maintenance of the indexes may grow well beyond the maintenance window or might not even finish in a 24 hour period.
Changes to the structure of tables and the addition of indexes should be surgical in nature especially at first.
With the question in mind of "How do you eat an elephant", it may actually be easier to build another database and begin migrations of table corrections and normalized data one functional group at a time. Think of it as an online total rebuild. Synonyms in the original database would be used to point to the new corrected structures as they become available. Eventually, everything will have been corrected or identified as already correct, the old database will be empty, and the apps will all have been modified or rewritten to use the new database.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 2, 2014 at 11:11 am
As Jeff said if you can upgrade this database first because investigating the performance problems will be so much easier. However that may take a while because you will need to test the application against the new version thoroughly first, so it is worth making a start whilst at 2000.
Use the the wizard to give you an idea and a starting point. When you compare its recommendations to the profiler results some of the tie ups between the suggested indexes and the the queries that would benefit from them will jump out at you.
I think you have to start with profiler here, but don't just look for the longest running queries as they may not run that often, look also for the queries that run the most often and see if they can be improved. If you have no indexes at all look first to add a clustered index to all tables, if nothing else it will enable you to compact the heaps you currently have, thus saving i/o and space, which will counteract any extra space used by non-clustered indexes. Then look for non-clustered queries that can cover your main queries and ensure your queries are SARGable.
You will need to look at the datatypes to avoid your indexes being unnecessarily wide and save disk space, but at that point you will need to check carefully your app still works and results are identical.
---------------------------------------------------------------------
March 2, 2014 at 11:13 am
Jeff Moden (3/2/2014)
Jake Shelton (3/2/2014)
Thank you Sean - again, is it worth changing the table structures? Also, will we need to point the sprocs to the new indices?BTW - we are porting these DB's in due course to an SS2K8 box, so hopefully we'll also add some DMV's at the other end.
Although you can use table "hints" to "point" procs at indexes, it usually doesn't help. In most cases, if the procs don't use available indexes, it's because they can't use them effectively because of the condition of the underlying code or the datatypes in a proc.
To be honest, my first step would be to forget about table, proc, and index changes for now and get the database moved to the 2K8 box where you can take advantage of modern features to make the database and the related app better.
Good idea in theory, but we've got users in pain now, and the target box won't be ready for a few weeks, months possibly. Yes we may have to redo the indexing at the target end too, but hey-ho....
Once that's done, then...
Yes, it's definitely worth changing the table structures and the datatypes if performance and data integrity is to ever be achieved. Do understand that if the database is in that bad of a condition, then the front-end code is probably equally deplorable or worse and that even changing a DATETIME column from VARCHAR(8000) to a DATETIME datatype could break a whole lot of "managed" code. That shouldn't stop you and your team but it does need to be planned for.
Indeed we will - I get what you're saying about doing what we can, and letting go of what we can't.
I agree with what Sean said about DTA. It's a great way to get a quick list of things that you need to look at but (IMHO) the indexes that it suggests to be created are usually riddled with full covering indexes that contain many INCLUDE columns. The important thing to remember is that all non-clustered indexes are nothing more than a sorted duplication of data. Multiple wide covering indexes can easily double or triple (typical, it can be much worse) the size of a database, make inserts, updates, and deletes horribly slow (all the indexes have to be updated for inserts and deletes) to the point of getting insurmountable timeouts and increases in deadlocks.
Will take your advice about the advisor...under advisement. 🙂
If memory serves, the smart thing to do 'generally' is to index columns involved in joins - any other good candidates for N/c indices?
Because of the duplication of data, the backups may no longer fit on their disks, tape backups will take much longer, emergency restores will take much longer, making copies of the database for development may become impossible because they'll no longer fit on the Dev boxes, and nightly maintenance of the indexes may grow well beyond the maintenance window or might not even finish in a 24 hour period.
Got it.
Changes to the structure of tables and the addition of indexes should be surgical in nature especially at first.
With the question in mind of "How do you eat an elephant", it may actually be easier to build another database and begin migrations of table corrections and normalized data one functional group at a time.
Actually I'm about to undertake a private project involving restructuring the schema, currently it looks like the dev team who configured it did so late on Friday after having spent 4 hours in the bar...
March 2, 2014 at 11:18 am
If memory serves, the smart thing to do 'generally' is to index columns involved in joins - any other good candidates for N/c indices?
joins are often on columns defines as primary and foreign keys, so those are deifinite candidates, but I have a feeling this is where you say none have been defined.........................
---------------------------------------------------------------------
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply