December 23, 2008 at 2:45 pm
Hi Everyone! This is my first post to what looks like a great forum (I was recommended by a friend)
Anyways, we are running SQL Server 2005 with SP 2.
Select statements are taking excruciatingly long on the production database server in comparison to our test environments. We have approximately 14 million records spread across 3 tables. 2 Tables consist of about 3.xx million records each, and the other table consists of about 7.xx million records.
These tables consist of business data that we are querying. Our average query ranges from 20 results - 1,000 results on the query. In the past, smaller results took between 250 and 750 milliseconds, and larger result sets clocked in at about 1250 milliseconds.
We recently did a table redesign which massively fragmented the drive. After doing a double-defrag of the drive our query speeds lifted (although still not acceptable). I have also done a DBCC CheckDB on the culprit database which returned clean.
Anyways, any help is greatly appreciated! I can zip execution plans and post them. Also I have come to the conclusion that are hardware is not the limiting factor. We are on a 2core 3ghz system with 2GB of ram. On basic select statements our system can pull 1.05 million records a minute.
In the meantime I will be running reindex's on the culprit tables
December 23, 2008 at 2:53 pm
Sounds like a reindex issue, post back after you finish reindexing...
* Noel
December 23, 2008 at 3:22 pm
Will do, running the query right now. Thanks 🙂
December 23, 2008 at 6:09 pm
Did you view the statistics to see whether SQL server is using the required indexes? Did you update the statistics? Did you check whether Autostats option is ON?There is a high probability that is sounds as reindexing problem but you could try above as well ...
December 23, 2008 at 6:15 pm
Required indices -- Being used
Stats were updated after the reindex
I will check on Autostats right now
*EDIT* Autostats on
December 23, 2008 at 9:58 pm
Post the execution plans (in .sqlplan form) as a start. If you can also post table definitions and the queries that would help.
December 24, 2008 at 6:19 am
matt stockham (12/23/2008)
Post the execution plans (in .sqlplan form) as a start. If you can also post table definitions and the queries that would help.
Don't forget the indexes in the definitions too 😉
December 24, 2008 at 10:46 am
Ok guys! Thanks so much for helping out.
If you don't mind please check back after Christmas 🙂
I'm just at the office to wrap a few things up for an hour or two, so I will post table definitions with indexes and execution plans on the 26th, or 27th.
Once again thank you so much for the help 🙂
December 30, 2008 at 4:09 am
hi as per all other told try reindex and also tell that in your table how many columns do u have if table has so many columns and also as much data then it can also down your performance
Raj Acharya
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply