Hi Mates,
It has been almost 2 months since I posted something in my blog. In this post I
would like to share one of the performance issues of what we had in my
Environment.
I wonder how we will work without our beloved SQL giants say (Paul Randal &
his Team, Brent Ozar and many more). Their contribution to SQLServer is immense
and it's helping DBA's to achieve their tasks.
Coming to the problem I got an invitation to join a performance issue and they observed the transactions are getting timed out. Eventually everyone feels it's Database issue.
Unfortunately, I do not know much about the Environment and we got the access to the databases recently.
When checked I could see blocking however it did not last for more than 5 seconds. I came to know that environment handles card processing which means every transaction has to happen with in 15 seconds.
So Back to title how SP_BlitzCache helped me? When I ran the SP I could see one of the stored procedures being prone to Deadlocks and Blocking.
Upon Looking further this SP has some few 100 lines of code and one of the table has got only one clustered index.
After running SP_BlitzCache I could see this SP is going for trivial optimization instead of full.
If you are aware of trivial plans then you can read further if not below is the link which explains about trivial plans
https://www.erikdarlingdata.com/tasting-menu/the-sql-server-performance-tasting-menu-trivial-plans/
That SP is getting executed quite frequently (57 executions per minute) and there are too many Lock Escalations too.
What is the problem with trivial plans? It will not give us missing index recommendation.
My initial suggestion is to make use of Trace flag 8757 OPTION(QUERYTRACEON 8757).
I made use of this in lower environment and re-run the query and as expected the execution plan come up with Missing Index recommendation.
so now the question is should we make use of trace flag all the time to achieve Full optimization? Well, not really once you create that missing non clustered index you don't need that trace flag any more as SQL Engine will go for FULL optimization automatically.
Before the index creation we used to get Deadlock and Blocking alerts quite frequently after that all the alerts got hassle free.
Once again I would like to thanks Brent Ozar for his great help to the SQL Community.
Comments