March 14, 2017 at 11:13 am
We are running SQL Server 2012 (SP1) - 11.0.3000.0 and we have an interesting problem.
Our customer reported that some certificates took a long time to load and others were quick.
We tried to recreate the problem in a test environment with a complete backup of the production DB, but all certificates were fast.
The production DB and the test DB are the only databases on the server.
We restarted SQL Server and now all certificates load fast.
I'm not a DBA, so I am looking for advice on how to track down this problem.
This issue will return, so what tools I can use to help identify the problem when it does?
Why did the restart help? Is it some internal cache that got cleared? Connections reset? Something else?
Thanks.
March 14, 2017 at 11:22 am
Sounds to me like the plan cache got cleared out and things improved.
Might want to check on the age of your statistics and possibly schedule an update on them. Chances are they are out of date.
Did you restart the entire machine or just the SQL Server instance?
Do you have permissions and access to alter the SQL queries or is this a 3rd party application? If you can change things, might want to look at adding in query hints such as OPTIMIZE FOR UNKNOWN.
Some tools that could help are a consultant. If that is outside of your budget, hiring a DBA could probably help.
Failing that, I would be looking at extended events and watching what is happening.
Does the application requesting the certificates get and maintain a connection? If so, restarting SQL Server would blow out all existing connections.
Might want to also check what the memory utilization was and what SQL has configured as the max memory and ensure there is enough free memory for both SQL, the OS, and any other things that run on that machine (SSRS, SSAS, AntiVirus, etc). Your AV could be causing some slowdown while reading/writing to the data files too.
My opinion though - a consultant and/or DBA would likely help you the most.
Since some of the certificates are fast and some are slow, I expect this is a parameter sniffing problem and adding in OPTIMIZE FOR UNKNOWN or adding in OPTION(RECOMPILE) will help. Especially if you have optimize for ad-hoc queries turned on as then it won't save the query plan for one-use queries.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
March 14, 2017 at 11:56 am
Since you didn't do anything other than reboot (such as add an index, update statistics, etc) then it is almost certainly a parameter sniffing issue. Adding to this likely hood is that some executions ran fast and others were slow.
I would almost never use OPTIMIZE FOR UNKNOWN. You are GUARANTEEING that some of the time you will have a (potentially disastrously) bad plan.
What I WILL do is jump through hoops to trade CPU ticks for just about ANYTHING - an here it is for the avoidance of the bad plans. So I would try OPTION (RECOMPILE) on the statement that is going out to lunch some of the time and see how that goes with a variety of calls.
Speaking of which, check your data distribution. My guess is that you have a skew problem so some value gets a very small number of rows and others get relatively large numbers. Storing the plan for either makes the other side of the coin horrible.
Oh, I suppose your base problem could have been simple blocking, but I bet not, especially on the test box. If you believe it could be then sp_whoisactive is the tool of choice to see that (and a LOT more) in real-time.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 14, 2017 at 1:56 pm
That is good to know. I knew they both did different things, but I was under the impression that if your parameter was going to be unique for each query, that optimize for unknown would be a suitable approach.
That being said, I just assumed that a certificate would be unique. Probably not a very safe assumption.
How frequently do you update statistics? are they out of date? That may also be a good place to check. Bad statistics == bad query plans.
Although to me this does sound like parameter sniffing problem and with(recompile) (if you are allowed to add that) would fix it.
If not, you may get better performance with some index tuning and more frequent statistics updates.
Or a consultant. I seem to be recommending that a lot, but they are the experts. Or hiring a DBA.
Although, thinking about the blocking... do you have any tools in place to monitor your SQL Server(s) in realtime? If not, it might not hurt to invest in something.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
March 16, 2017 at 4:35 am
Can I just mention that having a Test database on the same server as a Production database (whether or not they are on the same instance) is a Really Bad Idea?
If you can, please move the Test db off the server. It could be causing performance issues with Production due to runaway processes that consume the CPU or memory. Even if it's not, it's never a good idea to combine non-Prod with Prod because all it takes is someone being attached to the wrong db, typing "DROP DATABASE" or "DROP TABLE", and suddenly Production is screwed.
March 16, 2017 at 8:26 am
Brandie Tarvin - Thursday, March 16, 2017 4:35 AMCan I just mention that having a Test database on the same server as a Production database (whether or not they are on the same instance) is a Really Bad Idea?If you can, please move the Test db off the server. It could be causing performance issues with Production due to runaway processes that consume the CPU or memory. Even if it's not, it's never a good idea to combine non-Prod with Prod because all it takes is someone being attached to the wrong db, typing "DROP DATABASE" or "DROP TABLE", and suddenly Production is screwed.
I can't tell you the number of clients I have busted for this!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 16, 2017 at 12:26 pm
TheSQLGuru - Thursday, March 16, 2017 8:26 AMBrandie Tarvin - Thursday, March 16, 2017 4:35 AMCan I just mention that having a Test database on the same server as a Production database (whether or not they are on the same instance) is a Really Bad Idea?If you can, please move the Test db off the server. It could be causing performance issues with Production due to runaway processes that consume the CPU or memory. Even if it's not, it's never a good idea to combine non-Prod with Prod because all it takes is someone being attached to the wrong db, typing "DROP DATABASE" or "DROP TABLE", and suddenly Production is screwed.
I can't tell you the number of clients I have busted for this!
I learned this when I was first learning SQL server. I didn't have a DBA job yet, had built a SOHO out of 2 computers bought cheap from computer shows and had a bunch of DBs on the same instance. BOY DID I HOWL the first time I realized I was connected to the wrong database. Had to rebuild that puppy from scratch. Then I realized, hey, this breaking stuff is great for learning... So I went on to break the server so I could rebuild that too.
But I never forgot that first lesson. Pay attention to what you're connected to and try not to mix up environments.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply