How to dynamically determine the correct Process\Working Set performance counter instance

The Process performance counter object uses the following #1, #2 naming scheme whenever multiple instances of a process with the same executable name are running. \Process(sqlservr)\Working Set: this may be a default or named instance of SQL \Process(sqlservr#1)\Working Set: this may be a default or named instance of SQL \Process(sqlservr#2)\Working Set: this may be a…

0

Duplicate SPN: What is it really?

If you look at many of the references regarding Kerberos troubleshooting, one of the common recommendations is to verify duplicate service principal names (SPNs) do not exist. I mention this myself in the SQL Server Kerberos and SPN Field Guide here. But what do we really mean by that?   SPN is a String Attribute of an…

1

Understanding User Account Control (UAC), A Brief Summary

Below are some notes I took while troubleshooting a UAC issue. I found it useful to understand the difference between Rights and Permissions, as well as how to identify when UAC was coming into play. What is the Difference Between Permissions and Rights (Privileges)? Permissions apply to Objects A permission is an authorization to perform…

0

Troubleshoot Runaway Queries in SQL 2014 Using sys.dm_exec_query_profiles

Scenario You are tasked to identify the bottleneck of a slow or problematic T-SQL query which either never finishes, takes many hours to complete, or fails after many hours (for example with a tempdb space error). You would like to view execution plan details and runtime statistics while the query is still executing. The Challenge…

1

How to Start or Stop SQL Services without OS Admin Rights

Scenario You wish to grant users who do NOT have OS administrative rights the ability to start/stop SQL Server & SQL Agent services. Please review the following related blog post: http://blogs.msdn.com/b/sqlupdates/archive/2015/05/14/how-to-start-or-stop-sql-services-without-uac-prompts.aspx (Note: to see the pictures below at a better resolution, please click on the picture and this will open a link to the source…

0

How to Start or Stop SQL Services without UAC Prompts

Scenario You have OS administrative rights and would like to start/stop SQL Server & SQL Agent services via a script without getting any UAC elevation prompts in an environment where UAC is enabled. Please review this related blog post for steps to grant non-admin users permissions to stop/start SQL Services: http://blogs.msdn.com/b/sqlupdates/archive/2015/05/14/how-to-start-or-stop-sql-services-without-os-admin-rights.aspx. For an overview of…

0

PowerShell Lessons Learned from Building an Automated SQL Installation and Patch Management Implementation

Attached are the PowerPoint slides from the recent presentation I gave for the SQL Server User’s Group meeting.  Thank you all for attending! Download the presentation here. <Extracted Slide Text> PowerShell Lessons Learned from Building an Automated SQL Installation and Patch Management Implementation Presented by: Fany Carolina Vargas, Microsoft Corp., Sr. PFE, SQL Dedicated Support…

0

Guidelines for Setting SQL Permissions on Mount Point Folders

You can use Windows Disk Management to assign a mount-point folder path (rather than a drive letter) to a volume. In this manner you can access a new volume via a child folder path of a different volume’s drive letter. For example, say you have three 100GB volumes Vol1, Vol2, Vol3. We can initially mount…

0

SQL Server Kerberos and SPN Quick Reference

  This post contains the references and methodology I use when troubleshooting SQL Kerberos issues. While there are already many SQL Kerberos articles in existence, the goal here is to provide a central and up-to-date resource(including new tools + updated tool syntax) which covers not just the configuration steps, but an understanding of why this…

5