| Author |
Topic |
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
 Posted - 2004-11-04 : 19:48:43
|
| Due to it being a long time since I did it last, and various changes to table structures about a week ago I decided it was time to re-index my database. I used index tuning wizard and decided to go with the drop all indexes options for maximum performance. I think this turned out to be a mistake however. One table in particular is returning results in horrible times. It's a table of 500,000 rows with just two INT columns.I have tried rerunning Index Tuning wizard on the live server, however it always freezes. I have just downloaded and restored the database to my developement server.I assume I can run the index tuning wizard no problem now with the trace data off the live server, but how do I get the index recommendations that are made to be transferred to the live server? I have no use in creating them on the development server I will be testing on.Hopefully thats somewhat clear ;)Thanks againmike123 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-11-04 : 20:18:15
|
| I only use the index tuning wizard to see if it suggests anything else that I am missing. I never have it make the changes for me. Is there an option in there to script out the changes? If so, then you should be able to just run that script on the other system.Tara |
 |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2004-11-04 : 20:30:59
|
| restore a copy of your database from before the "accident" to a dev server. You can then script out the indexes that were dropped, and apply them back to production.I would suggest in the future that you perform Profiler traces looking for SQL that takes longer than 1 second to run. You should also look for SQL that reads more than 5000 blocks, or takes more than 1000 CPU. This will help you narrow down and locate the bad SQL that you might be able to tune.If you find something particularly bad, post it here and we can try to help.-ec |
|
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2004-11-05 : 00:07:56
|
| thanks for the tips .. im following your directions and will report back soon ....I already see a query with 155178 reads ..... yikes! However this was the query I was worried about and I am hoping indexes will fix it.What do you guys think of using the table datatype instead of temptables for paging ? I remember reading something about it recently and it seemed like a really good idea, and that it might be alot faster. Would this reduce my reads?I plan on converting all my SPROC's that use the temptable template for paging to this method (as soon as I find the article again ) before I even think to start tuning.Any thoughts?Thanks very much!mike123 |
|
|
|
hgorijal
Constraint Violating Yak Guru
277 Posts |
Posted - 2004-11-05 : 04:29:35
|
| If you know which query is taking so long, why don't you look at the query plan of the query to see how bad it is, what idx's it is using and that would give you a better picture of what indexes you might want to create to make it better.Table variables are faster than temp tables as they are stored in RAM. Again, they are stored in RAM; so beware of the size of these table variables. You don't want to load thousands of rows into table variables and fill up all your RAM. This would make things even worse.Hemanth GorijalaExchange a Dollar, we still have ONE each._______Exchange an Idea, we both have TWO each. |
|
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2004-11-05 : 05:08:08
|
| This query is one of the simpler ones, just 2 joins selecting 3 rows. I'm currently indexing and we'll see how that improves it. I'll post results if its not significantly better. I do have some other queries that I'll probably post to analyze the indexes.Would you consider the paging in the forums such as these a good candidate for converting temptables to table datatypes? I ran the code to create the indexes from QA. It's been running for 4 hours, does this mean somethings wrong or should I just give it as long as it needs. It's creating 4 indexes over 2 tables. 550,000 rows and 75,000 rows.Thanks again!mike123 |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-11-05 : 12:20:00
|
| About the table variable versus temp table thing, table variables are recommended when the data inside it will be 10,000 rows or less.Tara |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-11-05 : 12:36:59
|
| I put these before/after the query I am testingSET STATISTICS IO ONSET STATISTICS TIME ON... put query hereSET STATISTICS IO OFFSET STATISTICS TIME OFFand then run that "snippet" to see what the results are. You do, of course, have to find out what the actual parameters to the query would be in practice (using SQL Profiler or logging querries from your application etc.)I then experiment trying to get the ScanCount and LogicalReads to smaller numbers. The "actual" numbers are obviously influenced by cache state etc., and I ignore them.Kristen |
|
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2004-11-05 : 14:33:52
|
| Tara, great that means my queries are perfect candidates........ I will play with the temp tables and post before and afters ... Kristen thanks for the tip .....My SPROC that created the index has now been running for 13 hours, is something likely wrong ? for sure wrong? probably ok?Thanks alotmike123 |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-11-06 : 02:03:59
|
| I think if you stop it then it will roll back - which will take as long again :-(Kristen |
|
|
|
|
best practice with on running trace