Published two posts on the Tiger team blog for Change Tracking cleanup:
Tag Archives: Troubleshooting
SQL Saturday 511: Extended Events and more
Extended Events have evolved a lot since they were first introduced in SQL Server 2008. And today, there are multiple extended events available in SQL Server which allow you to debug common scenarios without collecting having to collect memory dumps or diagnostic data which can be detrimental to your SQL Server instance’s performance.
On April 2nd, join me at Redmond to understand how to leverage the new extended events that are available in SQL Server 2012 Service Pack 3 and above in your environments. I will talk about common scenarios where the new extended events are available and provide canned scripts to help collect and analyze data for complex scenarios.
There are a host of SQL experts and as well as folks from the Microsoft SQL Server product group who will be at the event to present and answer questions that you have! Looking forward to meet the SQL Community at the event!
WHEN: April 2nd, 2016, 2.15 – 3.15 PM PST
SESSION: Troubleshooting made easier using Extended events
Details about the session are available here. The full schedule of the event is available here.
The slide deck that I used during the session is available below.
24HOP Session: How to be a Ninja – Troubleshooting SQL PERF on Azure VMs
I have been a bit quiet on this blog but that is partly due to the fact that I have moved into a new role and a new country. I am now a part of the SQL Server Product Group [b|t] and based out of Redmond.
I am actually really excited about this. Sourabh Agarwal [b|t] and I are going to be presenting a preview to our SQL PASS Summit 2015 pre-con session "How to be a Ninja: Troubleshooting SQL performance on Azure Virtual Machines". Yes, we are starting the initiation program of becoming a SQL performance troubleshooting NINJA on SQL Server!
Troubleshooting is an art but the tricks of the trade changed with the advent of Azure Virtual Machines. Performance troubleshooting is different and at the same time very similar to what you have been used to for SQL Server. SQL Server performance on Azure VMs can be a sore point for many as the host troubleshooting entry points are limited and the knowledge of the internal workings scarce.
In this session, we will show you what best practices should be known for SQL Server instances running on Azure Virtual Machines! We will talk about tips on automating the implementation of all these best practices during deployment making this a single one-click deployment. This session will be a pre-cursor to our pre-con where we will go the whole nine yards and detail how to automate deployments from scratch, implement best practices automatically and analyze performance issues magically!
We hope you can join us for this session online and we do hope to see you during our pre-con! The 24Hop sessions are full of great sessions from great speakers in the SQL Family. See the full list here. I would recommend looking through the list and signing up for the ones that you are interested in. This will also give you a preview of what you can expect in the SQL PASS Summit this year.
This 24 Hours of PASS: Summit 2015 Preview event takes place over 24 hours, beginning September 17, 2015, 12:00 UTC. Featuring 24 webcasts delivered over 24 hours, this event provides a glimpse into the unparalleled content on offer at PASS Summit 2015, October 27-30, in Seattle, WA.
WHEN: September 17th at 8AM PST (3PM GMT)
WHERE: ONLINE
Facebook Event for our session: https://www.facebook.com/events/938656286172663/
Registration link for the event: http://www.sqlpass.org/24hours/2015/summitpreview/Registration.aspx
[UPDATE] September 29th, 2015
Thank you for the feedback that you shared after the session. It is always great to know what people liked in the session and even better to know where we need to improve. This helps ensure that our next iteration has the necessary tweaks. We received an overall 90% positive feedback and we thank everyone who attended for that!
The replies to the questions from the session are available below.
Q. Regarding the performance fixes as best practices(hotfixes/CU), do we have separate hotfixes(.msi/msp) for azure environment when compared to on premises environment?
A. The SQL Server installation bits that you would run on Azure VMs and on virtualized/physical on-premises environment are the same. So there aren’t any different set of fixes that need to run on Azure VMs.
Q. Are these Cheat Sheets available online?
A. The cheat sheets are available in the presentation PDF on the 24HOP site.
Q. Is using "Lock Pages in Memory" lead to that total allocated memory amount of SQL Server process is not seen in Windows Task Manager?
A. Task manager is not a good place to look for allocated memory when you want to find out allocations made after enabling Lock Pages in Memory privilege for the SQL Server service account. You could either look at Total Server Memory perfmon counter or the memory DMVs to track SQL Server memory usage. Additional reference: https://msdn.microsoft.com/en-us/library/ms176018.aspx
Q. Why are you disabling caching on the log file drive?
A. This is due to the IO patterns that the SQL Server transaction log file receives and how Azure storage is structured. We have seen in tests that the performance for SQL Server transaction log is best when write caching is disabled for disks which hosts transaction log files. We will talk about this in detail during our pre-con session.
Q. For Datawarehousing workloads, do you recommend lock pages in memory setting on on-premise/azure VM hosting SQL Server?
A. For on-premise workloads, we recommend you test and ascertain the needs before enabling Lock Pages in Memory (LPIM) privilege. For Azure VM workloads, the first important task is to pick the machine with the right SKU. We recommend enabling LPIM to prevent paging to the local disk on the rack which can negatively affect performance.
Q. Why are there different storage options based on Windows version? Is there any dependency on SQL versions?
A. There aren’t different storage options based on Windows version. The different storage options are based on the performance tier that you want to be on. It is Windows and SQL version and release agnostic.
Q. Can you let me know the resources on Azure Storage?
A. The Azure storage documentation is a good place to start for this. We will talk about this in detail in the IaaS introduction part of our pre-con.
If we have missed any question, please leave your question in the comment section of this post and we will answer it.
Lastly, we loved the notes that Matt Penny [t] took during our session. A screenshot of that is shown below. Thank you Matt! J The 24HOP session presentation is attached on the session page.
SQL Server 2012: Debugging Deadlocked Schedulers
I had blogged about debugging deadlocked schedulers earlier for SQL Server 2008 R2 and below releases. Since there have been some fundamental changes in SQL Server 2012, I thought it would be a good idea to post about debugging the same scenario on SQL Server 2012 instances.
Older posts:
Debugging Deadlocked Schedulers Part 1
Debugging Deadlocked Schedulers Part 2
CScript and RunAsAdmin
I had written a script a while back which would set the TCP/IP port for a SQL Server instance. Before you start throwing brick bats at me…. Powershell was not in existence in those days and yes…. doing the same tasks in Powershell is much easier! Phew… Now let me get back to my story!
One of my colleagues told me that the script was failing due with the following error message:
SQL_PortChange.vbs(52, 1) Microsoft VBScript runtime error
: Object required: ‘objOutParams‘
Now the above error is not the most intuitive of error messages considering the fact the object is being assigned a value in my VBscript. With a little bit of troubleshooting, we figured out that the RunAs Administrator (it can really catch you off-guard) option was not used to launch the command prompt.
So when running such VBscripts, do not forget to use RunAs Administrator option!
Now let us look under the hood a bit! I naturally was curious as to why the access denied message was not being thrown back to the user. I used Process Monitor to trace the registry activity of cscript.exe and wmiprvse.exe which actually works in the background to perform the tasks mentioned in the VBscript. I found that there were Access Denied messages in the Process Monitor trace but they were not being bubbled up to the user (see screenshot below)!
As you can see above, the access denied was on the SQL Server TCP/IP registry key and the WBEM keys. Since the registry key could not be read, the object was not created. And which is why we got the weird error listed above.
I thought this would be a good issue to blog on in case some one else ran into a similar issue!
TroubleshootingSQL 