February 9, 2011 at 10:05 pm
I created an index, did enough testing and it did improve the performance of the procedure.Now i come back on monday and performance is degraded? The same index is again doing scan? There hasn't been much change in the data. Now after going through different articles mentioned below are my thoughts.
i) Cant do re-compile since first compile takes more than 30 secs
ii) how do i use re-compile or any other query hint for a select statement inside the proc?
February 10, 2011 at 1:25 am
Post the table definition, index definition, query and execution plan (both seek and scan).
There is a recompile hint, but please don't start using hints unless you know what's causing the 'problem' and have exhausted all other solutions. Hints are for when you really, really, really know better than the query optimiser under all circumstances
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
February 10, 2011 at 10:42 am
GilaMonster (2/10/2011)
Post the table definition, index definition, query and execution plan (both seek and scan).There is a recompile hint, but please don't start using hints unless you know what's causing the 'problem' and have exhausted all other solutions. Hints are for when you really, really, really know better than the query optimiser under all circumstances
Gail thanks for reply. I apologize for asking this question, but is there a way i can just email you the details just because it has some confidential data in it? Thanks
February 10, 2011 at 10:55 am
sqldba_icon (2/10/2011)
Gail thanks for reply. I apologize for asking this question, but is there a way i can just email you the details just because it has some confidential data in it? Thanks
Seriously, how confidential can table and index names be? If you're really paranoid, open the exec plan in an xml editor and do a search and replace. Just make sure that it's still a valid sqlplan and that the fake names are consistent with your index definitions
I don't answer questions via mail because it means that I'm the only one who can help and you're the only one who benefits. I post here so that others can take over if I don't have time and so that anyone who has a related problem or interest can benefit.
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
February 10, 2011 at 11:20 am
GilaMonster (2/10/2011)
sqldba_icon (2/10/2011)
Gail thanks for reply. I apologize for asking this question, but is there a way i can just email you the details just because it has some confidential data in it? ThanksSeriously, how confidential can table and index names be? If you're really paranoid, open the exec plan in an xml editor and do a search and replace. Just make sure that it's still a valid sqlplan and that the fake names are consistent with your index definitions
I don't answer questions via mail because it means that I'm the only one who can help and you're the only one who benefits. I post here so that others can take over if I don't have time and so that anyone who has a related problem or interest can benefit.
Agree, will do so. Thanks
February 11, 2011 at 7:35 am
sqldba_icon (2/9/2011)
I created an index, did enough testing and it did improve the performance of the procedure.Now i come back on monday and performance is degraded? The same index is again doing scan? There hasn't been much change in the data. Now after going through different articles mentioned below are my thoughts.i) Cant do re-compile since first compile takes more than 30 secs
ii) how do i use re-compile or any other query hint for a select statement inside the proc?
1) I predict parameter sniffing here
2) I also predict that first compile isn't actually 30 seconds of compile time but rather that is the time it takes to pull data off the disk for first execution. If it IS 30 second compile time, it is time to clean up the sproc! 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 14, 2011 at 12:37 pm
seen this in the last month in my environment
we have a table and the devs complained about poor performance. got rid on a unique index that enforced a constraint on 5-7 columns and replaced it with another huge NC index. things were OK for a month until a big data change.
did some troubleshooting and i found recreating the clustered index worked even though it wasn't best practices since i tried it on a non-unique varchar column.
did some more troubleshooting where i looked at the data in detail and saw a lot of NULL's. turns out the developers for the app decided to use NULL's as real data
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply