May 12, 2010 at 2:01 pm
We have a vendor supported application and database that runs this query. Sometimes it runs fine, sometimes it is very slow. Initially we saw that it was having lot of threads so helped it by setting parellelism to 4 (from 16). That helped a little but not to the desired extent. The databases used have not been fragmented for a really long time and there are lot of table scans (although tables aren't huge) so we are working on getting a downtime to run reindexing. But I would like to know if that is indeed the problem. I have attached a zip file with the actual execution plan when it runs slowly. Thanks.
May 12, 2010 at 2:07 pm
Please post query, table definitions and index definitions, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 12, 2010 at 2:12 pm
Gail, I will try...we are a healthcare company and am not allowed to share DDLs and queries without permission. I will ask and see but am not too hopeful.
Thanks.
May 12, 2010 at 2:22 pm
Then I suggest you remove that execution plan ASAP, as I can work out the query, a portion of the table and index definitions just from that.
You can obfucate, change names, however the exec plan will still contain table names, schema names, index names and maybe a little bit of info about the data itself.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 12, 2010 at 2:25 pm
Thank you, just did.
May 12, 2010 at 2:35 pm
I'll delete the copy I downloaded, since you asked so nicely... 😉
See if you can get permission to post the stuff I asked for. Table defs, index defs, query and exec plan. Don't need any of the data. It's going to be very hard, maybe impossible, to give you useful advice without most of those.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 12, 2010 at 2:43 pm
Thank you, I realize the limitations but unfortunately we are not allowed to share those with anyone other than MS in support calls. After a hugely frustrating attempt at that was trying other means. Will stick to what I know and see if that helps any or ask advice on a theoritical basis. This query does a union join on multiple dbs, all of which have same structure. I can see huge number of table scans on some tables but these tables are small (under 20000 records) and i would not expect the optimiser to use an index on them. I will do the reindexing and see if that helps any and try to write up the problem the best i can if it doesn't . Thanks again for your prompt response, really appreciate it.
May 12, 2010 at 2:55 pm
dma-669038 (5/12/2010)
... but these tables are small (under 20000 records) and i would not expect the optimiser to use an index on them....
I would. 20000 is not small and it's certainly not in the range of 'not worth indexing'. That's usually under 100 rows, maybe even under 20, depending on the size of the rows. At 20000, SQL should be using indexes if it can.
I would guess that either the predicates are not SARGable or the index is not covering and the index insufficiently selective.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply