March 18, 2010 at 9:33 am
I am getting some timeout errors in particular sections of the application.......The related tables contain like 3 million records.....Does partitioning improve the performance???
(I think 3 million records are not that high for time out errors)
Thanks & Regards,
Sandeep
March 18, 2010 at 9:46 am
Partitioning can improve performance; however, 3 million rows in a table should not put you in a situation where your queries are timing out. I would not partition a 3 million row table. Effecitve indexing and good query writing should fix your problems.
Can you post your table DDL and your query code? If so, follow the link in my signature and post those items plus some sample data and we can help further.
March 19, 2010 at 8:17 am
The 3 million rows is likely not the most important factor. If you look at the execution plan of the request that is timing out you might find that it is doing a billion reads. If it is doing a table scan of the 3 million rows and an inner loops join against another table with 334,000 rows then it is effectively reading a billion rows albeit from memory assuming the pages can all fit in ram. I would set up a trace with profiler and record "Showplan XML", "sp:stmtCompleted", and "SP:StmtStarting" if the application is calling stored procedures.
Regards,
Toby
March 19, 2010 at 9:01 am
is time out coming frequently or any particular time ?
it means there might be chances that another process(example jobs/backup) running behind the scene
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
March 19, 2010 at 11:07 am
Yeah...i solved the problem....there were too many indexes on the table.........
March 21, 2010 at 11:16 pm
How did you resolve it ? Did you drop indexes ? i dont think you did that
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
March 21, 2010 at 11:56 pm
Yes, i dropped some of the indexes which are not useful
Regards,
Sandeep
March 22, 2010 at 3:10 am
Written by Paul White NZ for not dropping indexes
As far as the wider point is concerned, don't be too keen to drop apparently under-used indexes. Be absolutely sure you know which queries use the index, and what the impact will be if you drop it. Indexes are relatively cheap to update, compared to older versions of SQL Server.Rule of thumb: never drop an index, unless you are fully across every aspect of its use, and are prepared to get paged at 4am to trouble-shoot a problem caused by its non-existence. A particularly fun example would be where some code names the index in a hint.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
March 22, 2010 at 10:21 am
Looking back at the original post, I now see that we were not told of the nature of the timeout operation. I was assuming that a SELECT statement was the issue. Was this a SELECT problem or were you having problems with UPDATES/INSERTS/DELETES?
In addition to the posted quote from Paul White, index overhead is usually confined to UPDATE/INSERT/DELETE operations so removing the indexes shoudl not have much, if any, impact on SELECT performance. Unless, of course, there was a situation where you have indexes that cover the same columns.
I usually go through quite a bit of research before I create/drop an index to make sure of the overall system impact.
It sounds to me like, while droping indexes may have helped in this situation, that you should also take a look at tuning your SQL statements. Feel free to post the offending statement if you wish for our help. Please reference the link in my signature line for instructions on posting the SQL statement along with DDL and sample data.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply