Unlocking SQL Logins Without a Password

So today I broke something.  While prepping to bring our disaster recovery SQL servers in line with their production counterparts, I was going through the servers and adding linked servers that were enabled on the main production servers but not enabled/existing on their DR counterparts.  It seemed simple enough, especially since most of our linked servers are using pass through authentication of the user’s windows domain credentials to connect.  HOWEVER, in a handful of cases we are using SQL Server logins for the security context of linked servers.

The problem arose when I attempted to add a linked server, using a SQL Server login/password.  I had the wrong password (it was wrong in our password management application.. but that is another story entirely).  I added the linked server without incident since SQL Server doesn’t confirm the provided credentials before creating the linked servers.  Consequently, when I added the linked server with the wrong password, and then attempted to open the linked server in Management Studio a few times, the SQL Server login on the target server was locked out.  This resulted in a chain reaction as several production applications were using that login for their normal security context.

Needless to say, internal customers started complaining about security errors coming from a core business application “all of a sudden”.

As soon as I saw the emails, I knew it had something to do with me and what I was working on.  I checked the SQL Error log on the server in question and saw the obvious set of failed login attempts followed by a stream of locked out login error messages.  A bonehead move to be sure, but when I tried to unlock the account in management studio, I was told that I had to provide the password to the alter login screen if I wanted to unlock the account.  Sigh… not a real problem, more of an annoyance… or so I thought.  When I went in to our password management application, of course, the sql login information was NOT there.

The fastest way to get the cow out of the ditch was to disable the account policy on the SQL login first, and THEN unlock it using SSMS’s login properties window.

So i had to execute this simple command:


Disabling the CHECK_POLICY prevented SQL Server from trying to apply the Domain level controls on AD accounts and I was able to unlock the SQL Login after that.

We decided as a rule to leave CHECK_POLICY=OFF for a handful of SQL Server logins in our environment to avoid the scenario of a sloppy (but good intentioned) admin causing a critical SQL Server login to get locked out due to a misconfiguration.


Collecting xEvents Query Stats From Multiple SQL Instances to a Central Database Part 4

In this final post about xEvent based query monitoring data collection, I’ll cover the following:

  1. Running daily pre-aggregations of query stats by query hash and purging aging raw xEvent data
  2. Some useful sample queries for making sense of all the data you are now collecting.

Once you have a solution like this in place and large datasets are growing larger on your central database, you need to consider a data retention process.  Obviously you can’t keep a record of every execution of every sql statement in perpetuity, but you do want to maintain a balance between low granularity in the short term and high granularity over a longer period of time.  The way I do this is to have a couple extra tables that store daily aggregations of stats (by database and query hash).  Basically I run a job every day to calculate the aggregates by database and query hash (individual statements).  I added some basic data warehousing fields (day/week/month/year/etc.) and I am aggregating Execution count, CPU, Reads and Durations.  The table diagram below shows this:


The queries to generate rows for these tables are straightforward.  For example:

declare @StartDate Date=getdate()-1
declare @EndDate date
Select @EndDate = dateadd(day,1,@Startdate)– delete any data you already have for yesterday
— Run the group by query aganist the raw table XEvents_Queries
— and insert rows to the pre-aggregate table [XEvents_Queries_ByDBAggregate]
INSERT INTO [dbo].[XEvents_Queries_ByDBAggregate]
Select b.dbserverid, a.servername, datepart(year,a.timestamp) rowyear,datepart(month,a.timestamp) rowmonth,datepart(day,a.timestamp) rowDay,
datepart(week,a.timestamp) rowweek,datepart(weekday,a.timestamp) rowdayofweek,a.databasename, count(*) as executioncount,
sum(a.logical_reads) as TotalReads,
Avg(a.logical_reads) as AvgReads,
Max(a.logical_reads) as MaxReads,
sum(cpu_time_ms) as TotalCPU,
avg(cpu_time_ms) as AvgCPU,
max(cpu_time_ms) as MaxCPU,
sum(duration_ms) as TotalDuration,
avg(duration_ms) as AvgDuration,
max(duration_ms) as MaxDuration
from XEvents_Queries a
inner join DB_ServerMain b
on a.ServerName = b.ServerName
WHERE a.timestamp >= @Startdate
and a.timestamp < @EndDate
group by b.dbserverid, a.servername, datepart(year,a.timestamp) ,datepart(month,a.timestamp) ,datepart(day,a.timestamp) ,
datepart(week,a.timestamp) ,datepart(weekday,a.timestamp) ,a.databasename

The code above sets two variables (notice they are date and not datetime) for yesterday and today and then it generates the aggregate data (by databasename in the example above).  As designed, you can put the logic above into a sql agent job and run it at 12:01AM each day to generate the aggregates for the previous day. The whole script to populate both the database level and queryhash level aggregate tables can be found here:aggregatesonceaday.sql  This script will create the aggregate tables:preaggregatetablecreates.sql

Once you have these in place, you can easily build trend analysis and troubleshooting queries like this:


— return the  queries by database AND highest total reads per week


as instancename,



isnull(objectname,) as objectname,

isnull(objectname,) + ‘ – ‘ + sql as sql,

dateadd(week, rowweekofyear, dateadd(year, rowyear1900, 0)) 4

    datepart(dw, dateadd(week, rowweekofyear, dateadd(year, rowyear1900, 0)) 4) + 1 as WeekStart,


sum(TotalReads) TotalReads,

avg(AvgReads) AvgReads,

max(MaxReads) MaxReads,

sum(TotalCPU) TotalCPU,

avg(AvgCPU) AvgCPU,

max(MaxCPU) MaxCPU,


avg(AvgDuration) AvgDuration,

max(MaxDuration) MaxDuration

,RANK()  over (PARTITION by dateadd(week, rowweekofyear, dateadd(year, rowyear1900, 0)) 4

   datepart(dw, dateadd(week, rowweekofyear, dateadd(year, rowyear1900, 0)) 4) + 1 DatabaseName

   order by sum(TotalReads)  desc) as Rank

from XEvents_Queries_ByDBByQueryHashAggregate qs

WHERE dateadd(week,rowweekofyear,‘1/1/’+convert(varchar(10),rowyear)) > getdate()-35 — for the last 5 weeks

AND qs.servername =  ‘<servernamehere>’

group by





isnull(objectname,) + ‘ – ‘ + sql,

dateadd(week, rowweekofyear, dateadd(year, rowyear1900, 0)) 4 – datepart(dw, dateadd(week, rowweekofyear, dateadd(year, rowyear1900, 0)) 4) + 1


And this… to get the aggregates by database only (so you can get a basic “how active is this database in general?” type metric:

— return the  query metrics by database only

select dbserverid,



dateadd(week, rowweek, dateadd(year, rowyear1900, 0)) 4

datepart(dw, dateadd(week, rowweek, dateadd(year, rowyear1900, 0)) 4) + 1 as StartDate,


sum(TotalReads) TotalReads,

avg(AvgReads) AvgReads,

max(MaxReads) MaxReads,

sum(TotalCPU) TotalCPU,

avg(AvgCPU) AvgCPU,

max(MaxCPU) MaxCPU,


avg(AvgDuration) AvgDuration,

max(MaxDuration) MaxDuration

from emdw..XEvents_Queries_ByDBAggregate

WHERE dateadd(week,rowweek,‘1/1/’+convert(varchar(10),rowyear)) > getdate()-35

AND servername =  ‘<servernamehere>’

group by




dateadd(week, rowweek, dateadd(year, rowyear1900, 0)) 4

datepart(dw, dateadd(week, rowweek, dateadd(year, rowyear1900, 0)) 4) + 1

order by dateadd(week, rowweek, dateadd(year, rowyear1900, 0)) 4

datepart(dw, dateadd(week, rowweek, dateadd(year, rowyear1900, 0)) 4) +desc

This should get you going and please post comments with any questions or requests for clarifications.

Collecting xEvents Query Stats From Multiple SQL Instances to a Central Database Part 3

In this post on collecting xEvent query execution data to a central database I will cover the logic in the powershell script (provided as a link below) that determines the instances to collect from and then connect and collect the data to the central database.

First a refresher with an image from my previous post.

figure 1

As you can see above, the powershell script first queries the central database (DB_ServerMain table) to determine what instances to collect from.  Then it loops through those instances and executes the xEvents tSQL queries from my previous posts.  Let’s take a quick look at some parts of the PS1 file.

Determine What Instances to Collect From

At the top of the powershell file is a query and then the beginning of a loop:


The first part of the PS1 file opens a connection to the central database and executes the query against DB_ServerMain to determine which SQL instances to collect xEvent query data from. At the bottom of the snippet above, you can see that it assumes all .XEL files will be in “C:\DBAMonitoring\traces\” on the remote machine.  It them goes into a foreach loop iterating through the list of remote SQL Server instances to collect from.

Within the loop, a connection string for source (remote server) and destination (central collection server) are defined, some remote instance specific variables are set (for incremental loading) and then the SQL statement is defined dynamically:


Note that xel filename and fileoffset are embedded in the dynamic SQL prior to it’s execution.  Then the actual call to run the query on the remote server and save the results to the central server is executed in a TRY/CATCH block:


The code first defines the destination table name in the central DB instance (“XEvents_Queries”).  Then it opens the connection to the remote instance, creates a SqlCommand object (with the query AND the source connection) and then executes the query (with the .ExecuteReader() method of  the command object).  At this point, nothing has been written to the central collection database.  If there is an error in the execution of the query on the remote instance, then the CATCH block will fire.  In the powershell script, my catch block basically assumes there is a file rollover condition by checking for the phrase “The offset” in the caught exception: $ex1.Contains(“The offset”).  If the named xel file and offset are no longer valid (due to a file rollover), it basically resets the sql query to call the system function with NULLs instead of named incremental starting point (like this:   FROM sys.fn_xe_file_target_read_file(‘C:\DBAMonitoring\traces\sql2012*.xel’, null,NULL,NULL))

The final actionable piece of the script uses the SQLBulkCopy object to connect to the central collection database, and then write resultset from the remote instance to it:


Basically, it connected to the central collection server with the conneciton string declared at the beginning of the script.  It also defines the destination table, bacthsize and timeout values.  The .WriteToServer method is called with the $Reader variable that contains the resultset from the remote instance.  The .WriteToServer method will perform a BCP insert operation in chunks of 5000 rows.

That’s pretty much it for the powershell script.  You can download the full version of it here FetchQueryMonitorxEvents.ps1.

Collecting xEvents Query Stats From Multiple SQL Instances to a Central Database Part 2

In this post I will continue from the previous post about centralizing xEvents based query execution stats collection and reporting.   The key topics I will cover are:

1) Determine which sql instances you need to collect from and when the last collection return (to handle incremental loads throughout the day from the source servers to the central DB server)

2) Review of the xPath oriented queries that parse the .XEL files into a relational format for storage in the central DB

In my last post, I ended with discussing the XEL file’s offset functionality and how to use it within the sys.fn_xe_file_target_read_file system function to bring back a set of events “since the last poll”.  This functionality is what I use to perform incremental loads of xEvent query execution data from various remote sql servers into my central database.  First let’s take a look at the basic data model I am using:


DB_ServerMain has the name of the SQL Server and a flag (enablexEventQuerCollection) to denote whether it should be included in centralized collection.  Straightforward enough… you can get the list of SQL instances to collect xEvents from with a query like:

Select *
from DB_ServerMain
where enablexEventQueryCollection = 1

Recall from my previous post, that we can use the file_offset value in the .XEL file to basically run an incremental load (return everything after the specified offset).  In the data model presented above, you can see the XEvents_Queries table has the servername, file_name, and file_offset value (along with the query stats specifics).  In order to fully automate the collection process, we need the name of the SQL Server instance, the name of the last XEL file collected from AND the highest file_offset value within that last file that we have a record of.

Select  a.ServerName,isnull(xEventFile.xEventFile_Name,'NULL') as xEventFile_Name, isnull(convert(varchar(50),xEventFileOffset.xEventFile_Offset), 'NULL') as xEventFile_Offset 
from DB_ServerMain a 
OUTER APPLY (Select max(file_name) as xEventFile_Name from dbo.XEvents_Queries ws where s.servername = a.servername ) as xEventFile 
OUTER APPLY (Select max(file_offset) as xEventFile_Offset from dbo.XEvents_Queries ws where ws.servername = a.servername and ws.file_name = (Select max(file_name) as xEventFile_Name from dbo.XEvents_Queries ws where ws.servername = a.servername )) as xEventFileOffset

I’ll admit, it’s not the slickest code in the world, but it works to pull the servername, filename and file_offset for each instance to be monitored.  The OUTER APPLY(s) help in keeping it fast enough even after you have millions of rows in xEvent_Queries.    Once you know the instances you want to collect from (and the latest filename/fileoffset combination), you can connect to each SQL instance and select the contents of the .XEL files using the additional parameters in the call to sys.fn_xe_file_target_read_file() system function like this:

SELECT CAST(event_data AS XML) AS event_data_XML, file_name, file_offset INTO #Events FROM sys.fn_xe_file_target_read_file('C:\DBAMonitoring\traces\sql2012*.xel', null,'C:\DBAMonitoring\traces\sql2012_0_130295018340930000.xel', 141312) AS F

In the example above, the call will create (and fill) a temporary table called #Events with the alerttime and full event XML for all files that match the wildcard search “c:\DBAMonitoring\traces\sql2012*.xel”.  It will then start with the file and file_offset we specified (sql2012_0_130295018340930000.xel  and 141312) and disregard any earlier files or earlier entries within the file (based on the file_offset value).

The above query is basically the “incremental load” since the last poll, but it only pulls the raw XML event data (into an XML column) with the filename and file_offset associated with that event.  In order to make sense of the XML event, it needs to be parsed using xPath functions to pull out the individual elements (e.g. reads, writes, duration, etc).  The code below shows that logic:

 SELECT @@ServerName as ServerName, 
 event_data_XML.value ('(/event/action[@name=''query_hash'']/value)[1]', 'nvarchar(100)') AS query_hash, 
 DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), event_data_XML.value ('(/event/@timestamp)[1]', 'datetime' )) AS timestamp, 
 event_data_XML.value ('(/event/@name)[1]', 'vARCHAR(50)' ) AS EventName, 
 event_data_XML.value ('(/event/data [@name=''duration'' ]/value)[1]', 'int')/1000 AS duration_ms, 
 event_data_XML.value ('(/event/data [@name=''object_type'' ]/text)[1]', 'varchar(100)') AS object_type, 
 DB_Name(event_data_XML.value ('(/event/action [@name=''database_id'' ]/value)[1]', 'int')) AS DatabaseName, 
 CASE event_data_XML.value ('(/event/@name)[1]', 'vARCHAR(50)' ) 
 when 'sp_statement_completed' then event_data_XML.value ('(/event/data [@name=''object_name'' ]/value)[1]', 'NVARCHAR(4000)') 
 ELSE OBJECT_NAME(event_data_XML.value ('(/event/data [@name=''object_id'' ]/value)[1]', 'BIGINT'),event_data_XML.value ('(/event/action [@name=''database_id'' ]/value)[1]', 'int')) END as ObjectName, 
event_data_XML.value ('(/event/action [@name=''client_hostname'']/value)[1]', 'varchar(100)') as HostMachine, 
event_data_XML.value ('(/event/action [@name=''client_app_name'']/value)[1]', 'varchar(100)' ) as client_app_name, 
 event_data_XML.value ('(/event/action [@name=''nt_username'']/value)[1]', 'varchar(100)') as nt_username, 
 event_data_XML.value ('(/event/data [@name=''cpu_time'']/value)[1]', 'int')/1000 AS cpu_time_ms, 
 event_data_XML.value ('(/event/data [@name=''physical_reads'']/value)[1]', 'BIGINT') AS physical_reads, 
 event_data_XML.value ('(/event/data [@name=''logical_reads'' ]/value)[1]', 'BIGINT') AS logical_reads, 
 event_data_XML.value ('(/event/data [@name=''writes'' ]/value)[1]', 'BIGINT' ) AS writes, 
 event_data_XML.value ('(/event/data [@name=''row_count'' ]/value)[1]', 'BIGINT' ) AS row_count,
 CASE event_data_XML.value ('(/event/@name)[1]', 'vARCHAR(50)' ) 
 when 'sql_batch_completed' then event_data_XML.value ('(/event/data [@name=''batch_text'' ]/value)[1]', 'NVARCHAR(4000)') 
 ELSE event_data_XML.value ('(/event/data [@name=''statement'' ]/value)[1]', 'NVARCHAR(4000)') 
 END AS statement 
FROM #Events

The code above uses xPath to parse out the various elements of the XML event into usable tabular columns that match the order and structure of the xEvents_Queries table in the central database. The results look like this:


So now you have access to the reads/writes/cpu/durations as well as the statement within a larger batch or stored procedure that is using those resources.  You can run these type of queries on the host in question anytime to get a real-time look at the query activity, but pulling it all into a central database (with rows marked with an @@ServerName to delineate between instances) will be a more manageable solution for environments where there is more than one instance to keep an eye on (read: any environment)

With the information from this post and previous posts, you should be able to set up an active xEvents session to capture “statement complete” events.  You can then use the type of query similar to the above example to parse your captured xEvents data into easily consumable, aggregate-able (is that a word?) data sets for analysis.

I have used this type of setup on the local server to answer questions like this:

1) What are my top 5 executed tSQL statements on the server between 8:05AM and 10:30AM yesterday?

2) Which database had the most io/cpu usage for the last 6 hours?  Morning vs. Afternoon for the last 3 days? etc.

In my next post I’ll tie all of this together by going through a powershell ps1 file that has the above queries embedded into it and actually performs the write of query event rows from remote servers to the central database.

Collecting xEvents Query Stats From Multiple SQL Instances to a Central Database Part 1

In my previous post, I discussed setting up xEvents sessions in lieu of server side traces to collect statements level activity from a SQL Server instance.  I covered the basics of setting up the session to collect the sp_statement_complete and sql_statement_complete events to a .xel event file.  I also provided some queries that allow you to read the entries from all .xel files in directory and then parse the content into a regular tabular resultset for sorting, filtering, and aggregation.  While this is all very useful information to capture on a specific instance, most (if not all) of us in the DBA world are managing multiple SQL Server instances across production and non-production environments.  In this post I’ll cover the following topics:

1) Standardizing your xEvents sessions on individual sql instances to streamline the centralization process

2) Using powershell to automate the collection of xEvent data from multiple instances and store it in a set of centralized set of tables on one instance (a central management or monitoring sql server)

3) Automating an aggregation process to generate pre-aggregated table rows (by day, month, etc) for the query data we are collecting.

Standardize your Query Performance xEvents Sessions

In order to simplify the centralization of xEvent data, I adhered to the following simple configurations:

  1. All query performance xEvent sessions write out .xel file to the same named directory on any SQL instance.  In my case, I use c:\DBAMONITORING\traces for this.
  2. I configure the xEvent Session to write to 2MB files and rollover files after 40 have accumulated.  This ensures that I never have more that 80MB of disk space in use on  the local machine.  Later in the post, I’ll cover how I collect and centralize the data from the .xel files every 15 minutes so as long as you don’t product >80MB worth of .xel files every 15 minutes, you probably won’t lose captured event data between polls.
  3. All query performance xEvent .xel files should have the same prefix so they can be read into memory together.  In my case, all .xel files are started with SQL2012<somestring>.xel.

With these settings in place on each SQL 2012 instance that I want to collect xEvent query execution data from, I can use a powershell script executed on a central database instance that connects remotely to the monitored instances and pulls the recent xEvent data to a set of tables.

Centralized xEvent Processing and Collection

The diagram below illustrates the simple way that this solution works.

figure 1

The central database has a few tables to handle the following:

1) Control Table – has the names of the SQL instances that you would like to collect xEvent data from.  This list is used by the powershell script to determine which SQL instances to collect from each time that it runs.

2) xEvent Tables – These tables store the query performance information (xEvents) from the various servers in your environment.  Then you can run queries or write reports on top of this data to review instance specific query performance metrics.

The Powershell script performs the following actions:

1) Query the Control table to determine which instances to collect from.
2) Loop through the list of instances and for each one
a) connect and read the latest xEvent data from the .xel files on the server
b) Pre-process the data on the remote machine (in a temp table) and then load it directly into the xEvent tables in the central database

In my previous post (Capture and Review SQL 2012 Query Performance History with xEvents) I provided a TSQL script to run on a local instance that would read in the .xel files as XML strings to a temp table.  Then the second half of the query parsed the XML data into the familiar trace-like fields including:

Field Name Description
query_hash the unique query hash value that allows you to group identical calls without comparing/parsing the actual statement
timestamp time of the statement execution
EventName either sp_statement_completed or sql_statement_completed
duration_ms the duration in ms of the statement
object_type shows prepared or adhoc to indicate the type of call
DatabaseName the DB within which the statement was executed
ObjectName The name of the stored procedure called (if applicable)
cpu_time_ms total CPU ms used to complete the query
physical_reads physical read count required to complete the query
logical_reads logical read count required to complete the query
writes write count required to complete the query
row_count number of rows affected by the query
statement The actual statement that was executed

With those values above, you can run all kinds of grouping and time-windowed queries to see your most costly statements by cpu/reads/write/duration.  The only thing missing to centralize the content is to maintain an InstanceName with each record as well.  In the queries I make through the powershell script, I include an @@Servername as ServerName in the select list and few other control related fields including the .xel file name that the event came from and offset within the file that the event is part of.  Let’s take a quick step back and review how the .XEL files work in terms of logging xEvents to disk.

xEvents to Disk (.XEL file target), sys.fn_xe_file_target_read_file and Offsets

SQL Server provides a system function to read the data out of .xel files directly from TSQL.  The function is sys.fn_xe_file_target_read_file (documentation here: http://technet.microsoft.com/en-us/library/cc280743.aspx).  A basic call looks like this:

SELECT top 10000 file_name, file_offset, CAST(event_data AS XML) AS event_data_XML
FROM sys.fn_xe_file_target_read_file(‘C:\dbamonitoring\traces\sql2012*.xel’, null, null, null) AS F;

The above reads all of the files that start with “sql2012” and end with “.xel” in the directory and return a result set of events including the filename the event came from, the group offset number within the file (used for filtering) and an XML column of the full event data.  The results are shown below:


As you can see above, in an xel file, xEvents are logged in groups (denoted by a shared file_offset value).  When you call the  sys.fn_xe_file_target_read_file you can call it with a filename and a file_offset as parameters:

SELECT top 10000 file_name, file_offset, CAST(event_data AS XML) AS event_data_XML
FROM sys.fn_xe_file_target_read_file(‘C:\dbamonitoring\traces\sql2012*.xel’, null, ‘C:\dbamonitoring\traces\sql2012_0_130268187987170000.xel’, 775168) AS F;

In the example above, the select will return rows beginning with the specified file and file_offset and continue with rows from any subsequent files in the directory that match the sql2012*.xel search string.  Using this filtering functionality of the system function yielded better performance (in my limited tests) than comparing on an event timestamp which requires xpath-ing out the timestamp value and then a type conversion to make it a date for comparison.

part 2 coming soon….

Capture and Review SQL 2012 Query Performance History with xEvents

In this post, I’ll walk through the xEvents setup we have on a number of SQL 2012 instances. We use it to collect query performance metrics as a server-side trace replacement (note: this post assumes you are running SQL 2012+).  If you are familiar with server side tracing and/or profiler from 2008/2012, then a lot of this will make sense to you.  Basically I run an xEvent session that captures:

  • sp_statement_complete – individual statements within stored procedures that require more than 10 logical reads to complete
  • sql_statement_complete – individual statements within a sql batch (ad hoc) that require more than 10 logical reads to complete

We could just capture stored procedure completes and batch completes but going down to the statement level provides a few significant advantages (and challenges)


  • Statement level captures identify the specific sections within a larger stored procedure or batch which directs optimization efforts to the real slow elements.  If you capture events at the sp or batch completed level, then you’ll still need to do the work of finding the specific parts of the SP that are the slowest or most problematic.
  • In SQL 2012, sp and batch statement completed events include a query hash value so you can aggregate similar calls that use different parameters easily.  Back when we used server side traces to capture query execution information, i had to develop a complicated and resource intensive process that stripped raw sql calls of their parameter values and formatting so that they could then be aggregated for statistical analysis.


  • Statement level captures will take in much more data than the sp/batch completed events, so you need to make sure you plan accordingly (more on this later…)
  • Since there is a much higher volume of data collected, it is harder to keep a long term, highly granular history.  (more on this below)

So the first step is to set up an xEvent session on the server in question.  I use this code:

ADD EVENT sqlserver.sql_statement_completed(
sqlserver.plan_handle, sqlserver.nt_username,
WHERE (sqlserver.database_id >= 6 AND sqlserver.query_hash <> 0 )
),ADD EVENT sqlserver.sp_statement_completed(
sqlserver.plan_handle, sqlserver.nt_username,
WHERE sqlserver.database_id >= 6
AND sqlserver.query_hash <> 0)ADD TARGET package0.event_file(SET filename=N’C:\DBAMonitoring\traces\sql2012.xel’,

The code above creates an event session (names the events to capture and whatever filters we may have) but does not start the session. Some additional comments about the CREATE EVENT SESSION code above:

  • I capture sql_statement_completed and SP_statement_completed to capture SQL  statement level information (within ad hoc batches and stored procedures respectively).  My preference is to capture the lowest level query activity only (so I don’t have batchcompleted and spcompleted events cluttering the data set of individual sql statements.
  • You have the option to capture xEvents into the SQL Server Ring Buffer, but I have found the .xel files easier to deal with and allow for more fine grained control of retention.  As you see above, I am capturing xEvents to .xel files in the C:\DBAMonitoring\Traces folder on the sql instance in question.  I also limit files to 2MB each and no more than 40 files total (so a max disk footprint of 80Mb before older files are rolled over (aged out and deleted))
  • Since I am capturing query execution statistics for aggregations later, it is OK if I don’t collect every single event and I don’t want to collect events in a way that might impact performance so I set the EVENT_RETENTION_MODE=ALLOW_MULTIPLE_EVENT_LOSS setting on the event session.

Once you run the code above, the session is created on the SQL instance, but not started yet.  You can either run a start command in TSQL:

— start session
ALTER EVENT SESSION query_performance

or use SSMS to start the session

use SSMS to manage even sessions from within the Management folder of the instance

use SSMS to manage even sessions from within the Management folder of the instance

Once the xEvents Session is running you can see the .evt files collecting in the directory specified:

.evt files act just ilke the old .trc files ni that they reach a max size and then generate another file.  Once the rollover file count is met, then older files are deleted as new files are generated.

So just remember that the the “history” you can poll from active .evt files will be limited based on the volume of events captured and the sizing controls specified for the .evt file retention and rollover. You can query active .evt files much like you could with .trc files in SQL 2008 and 2005.  That said, I have found it easier to load the data into temp (or perm) tables from the evt files and then run additional queries for aggregations.

— copy event data into temp table #Events
— reads all .xel files that start with “sql2012” and end with “.xel” and puts each event into an XML column that we can query directly.
SELECT CAST(event_data AS XML) AS event_data_XML
INTO #Events
FROM sys.fn_xe_file_target_read_file(‘D:\DBAMonitoring\traces\sql2012*.xel’, null, null, null) AS F;

After the #Events table is populated, we can run the following XML xpath query to make ANOTHER temp table with the raw content separated out into individual columns.

— extract event details into a temp table #Queries
event_data_XML.value (‘(/event/action[@name=”query_hash” ]/value)[1]’, ‘nvarchar(4000)’ ) AS query_hash,
event_data_XML.value (‘(/event/@timestamp)[1]’, ‘datetime’ ) AS timestamp,
event_data_XML.value (‘(/event/@name)[1]’, ‘vARCHAR(50)’ ) AS EventName,
event_data_XML.value (‘(/event/data [@name=”duration” ]/value)[1]’, ‘int’ )/1000 AS duration_ms,
event_data_XML.value (‘(/event/data [@name=”object_type” ]/text)[1]’, ‘varchar(100)’ ) AS object_type,
DB_Name(event_data_XML.value (‘(/event/action [@name=”database_id” ]/value)[1]’, ‘int’ )) AS DatabaseName,
CASE event_data_XML.value (‘(/event/data [@name=”object_type” ]/text)[1]’, ‘varchar(100)’ )
when ‘PROC’ then OBJECT_NAME(event_data_XML.value (‘(/event/data [@name=”object_id” ]/value)[1]’, ‘BIGINT’))
END as ObjectName,
event_data_XML.value (‘(/event/data [@name=”cpu_time” ]/value)[1]’, ‘int’ )/1000 AS cpu_time_ms,
event_data_XML.value (‘(/event/data [@name=”physical_reads”]/value)[1]’, ‘BIGINT’ ) AS physical_reads,
event_data_XML.value (‘(/event/data [@name=”logical_reads” ]/value)[1]’, ‘BIGINT’ ) AS logical_reads,
event_data_XML.value (‘(/event/data [@name=”writes” ]/value)[1]’, ‘BIGINT’ ) AS writes,
event_data_XML.value (‘(/event/data [@name=”row_count” ]/value)[1]’, ‘BIGINT’ ) AS row_count,
event_data_XML.value (‘(/event/data [@name=”statement” ]/value)[1]’, ‘NVARCHAR(MAX)’) AS statement
INTO #Queries
FROM #Events

With the raw data now in tablular format (#Queries) you can run any number of aggregations based on the query_hash field.  The query_hash is a unique identifier for similar queries that run with different parameters.  It is a huge time saver compared to the old server side trace approach where you had to write your own “generecize the sql statement so I can group on it” functionality.

So with the #queries table in place, you can run the query below to return the specific statements (by query _hash) ordered by total logical reads:

— filter and include a sample query
WITH QueryHashTotals AS
SELECT query_hash,DatabaseName, ObjectName,EventName,
COUNT(*) AS num_queries,
SUM(logical_reads) AS sum_logical_reads
FROM #Queries
GROUP BY query_hash,DatabaseName, ObjectName,EventName
RunningTotals AS
SELECT query_hash,DatabaseName, ObjectName, EventName,num_queries, sum_logical_reads,
CAST(100. * sum_logical_reads
/ SUM(sum_logical_reads) OVER()
AS NUMERIC(5, 2)) AS pct,
CAST(100. * SUM(sum_logical_reads) OVER(ORDER BY sum_logical_reads DESC
/ SUM(sum_logical_reads) OVER()
AS NUMERIC(5, 2)) AS running_pct
FROM QueryHashTotals
SELECT RT.*, (SELECT TOP (1) statement
FROM #Queries AS Q
WHERE Q.query_hash = RT.query_hash) AS sample_statement
FROM RunningTotals AS RT
ORDER BY sum_logical_reads DESC;

With the data in tables you can pretty much do whatever your want with it in terms of aggregations and queries.  Only simple modifications to the query are required to return results by writes, duration, cpu, etc.  In addition to the aggregations you can derive (Top N Queries), you can also use the raw data for investigations (“What was the query stream leading up to my database issue”).  It all depends on how much data you can retain and at what level of granularity.

My next post will discuss how to roll out this xEvents based query monitoring solution to multiple machines and collect the data to centralized database for reporting across an entire environment of SQL Server 2012 instance.  In the meantime, if you have any questions or issues with the samples above, email me or comment and I’ll try to reply as soon as  can.

Call a Powershell PS1 File from a Stored Procedure

Last week I was working on a project to help us better identify over 600 databases we have in production.  The final solution wound up including the following components:

  • Store various contact people and internal information on the databases themselves as Extended Properties.  We are tagging all of our production databases with a business owner, system owner, and development lead, as well as information describing the application(s) that the database supports.  While extended properties are a rarely used functionality within SQL Server, they are a good fit for our purposes:
    1. Extended properties travel with the database, so when we restore production databases to our development and testing SQL instances (either weekly or nightly in most cases) the non-production database copies will automatically have the same extended properties.  This is beneficial for us because we only have to maintain the content on the production databases and the information will automatically “trickle down” to the non-production environments with our existing restore processes.
    2. Extended properties are simple, expandable and native to SQL Server.  Extended Properties are implemented as simple name/value pairs so they are extremely flexible.  We will easily be able to alter the types and number of tags we want to assign to the databases without extensive development efforts.  You can also view the DB extended properties by right clicking on any database in SSMS which is a nice to have for DBAs and SysAdmin troubleshooting issues either on the machine itself or with remote access via SSMS.
    3. Extended properties are backwards compatible to SQL 2005.  We still have a handful of SQL 2005 instances and a few dozen SQL 2008 instances in our environment, so we wanted a mechanism that would work the same for everything we are running today.
  • Establish a centralized dataset of the database properties to support a Reporting Services report that is current and usable by application support and other less technical or privileged teams (no SSMS access is most cases).  We often make database server performance information available to non-DBA team members with custom SSRS reports that render various monitoring data (session activity, wait stats, etc).  A report that allows users to review the internal contact people and applications associated with specific databases is very useful for our application support team to determine who to contact if there are issues or questions.

In the initial phase of this effort, we were provided a spreadsheet that was exported from our disaster recovery planning system with the application name, system owner, business owner, and lead developer.  This information didn’t include a direct link to individual databases, so we went through and updated the spreadsheet with servernames (instances) and DB names.  We then developed a powershell script to go through the spreadsheet, connect to the various remote servers and assign DB level extended properties to the database in question.  This was great for our initial effort, but as you might imagine, there were several applications that we couldn’t map directly to a database and we needed to work with various business users to identify the appropriate app/db mappings.  We got the initial dataset done and propagated out to the various databases, but we needed a simple mechanism to allow users to assign an app/app owners set to a database easily from a web based front end.

In the end, we decided to provide a stored procedure on our central monitoring database instance that would take application/owner information as parameters and add the database level extended properties on the remote servers automatically.  Since we already had a decent set of working code to do this in powershell (from the initial import), we decided to implement the procedure by having it call a powershell ps1 script with parameters to shell out from the SQL stored procedure by using sp_executesql.

The powershell file is attached at the end of this post for reference, but here is the syntax for calling the PS1 script file with arguments(parameters) from within a SQL stored procedure.

create procedure [dbo].[set_DatabaseProperties]
(@ServerName varchar(100) = NULL,
 @DatabaseName varchar(100) = NULL,
 @App varchar(500) = 'UNKNOWN',
 @AppDesc varchar(500) = 'UNKNOWN',
 @ApprovalUser varchar(500) = 'UNKNOWN',
 @BusinessFunction varchar(500) = 'UNKNOWN',
 @BusinessOwner varchar(500) = 'UNKNOWN',
 @Dept varchar(500) = 'UNKNOWN',
 @LeadDeveloper varchar(500) = 'UNKNOWN',
 @ManagedBy varchar(500) = 'UNKNOWN',
 @SystemOwner varchar(500) = 'UNKNOWN',
 @Type varchar(500) = 'UNKNOWN'
** 07/25/13 MRaftree Initial Creation

 DECLARE @CmdExecStr varchar(5000)
if (@ServerName is not null and @DatabaseName is not null and @App <> 'UNKNOWN')

-- build a command line string to execute the PS1 file with each argument in double quotes and separated by a space  Also, check the location of the powershell.exe file
--on your database server itself and make sure the path below is right
 Select @CmdExecStr = 'C:\WINDOWS\system32\WindowsPowerShell\v1.0\powershell.exe -File "C:\Powershell\SetDBProperties.ps1" "' + isnull(@ServerName,'Unknown') +'" "' + isnull(@DatabaseName,'Unknown') +'" "' + @App +'" "' + @AppDesc +'" "' + @ApprovalUser +'" "' + @BusinessFunction+'" "' + @BusinessOwner +'" "' + ISNULL(@Dept,'Unknown') +'" "' + @LeadDeveloper +'" "' + @ManagedBy +'" "' + @SystemOwner +'" "' + ISNULL(@Type,'Unknown') +'" '

 print @CmdExecStr
create table #returncode (value varchar(1000))
insert into #returncode exec master..xp_cmdshell @CMDExecStr
select * from #ReturnCode

Here is the ps1 file itself for reference:  SetDBProperties.ps1