Archive

Archive for the ‘Database Administration’ Category

SQL Server: How to fix error 7903

16/04/2013 1 comment

Recently I had to take care of the filestream corruption in SQL Server 2008 database, so I decided to share my experience. The error from DBCC CHECKDB was:

Msg 7903, Level 16, State 2, Line 1
Table error: The orphaned file “00005c8a-00006362-0001” was found in the FILESTREAM directory ID 9ae6ae63-bcc4-46f7-81cf-db99b01549e8 for object ID 903062353, index ID 1, partition ID 72057594125680640, column ID 11.

Explanation of the error 7903 is (from Cause and Resolution of Database Engine Errors):

“A FILESTREAM file was found in a FILESTREAM column directory; however, the corresponding column value in the partition is missing.”

This error cannot be repaired with CHECKDB so I decided to test what will happen if I physically delete mentioned filestream file. (You should take all user actions described in the document.)

I restored database to different server and executed DBCC CHECKDB again. It finished with the same error as I expected.

So I started to search for the document in the filestream directory of the database.

error_7903

First I copied the file to another location (I wanted to have a copy of the file) and then I deleted it. When I executed DBCC CHECKDB again it finished without errors.

Deleting the orphaned file will resolve filestream consistency error 7903 (If you decide to delete the file be sure that you don’t need it.).

If you decide to do that on a production SQL Server, you do so entirely at your own risk.

Advertisements

Compare tables between 2 databases with Tablediff and PowerShell

25/02/2013 Leave a comment

Few days ago, I needed to compare tables and find all differences in data between 2 databases on different SQL Server instances. For that task I decided to use the Tablediff utility which is included with SQL Server.

Tablediff utility can be used for schema and row counts comparison or a row by row comparison of data between 2 tables. It can generate T-SQL script to fix differences between 2 tables.  More information about tablediff utility you can find in BOL.

By default, tablediff utility can be found in the COM directory of SQL Server install folder.
For SQL Server 2012 default location is C:\Program Files\Microsoft SQL Server\110\COM\
For SQL Server 2008/2008 R2 default location is C:\Program Files\Microsoft SQL Server\100\COM\.

By default, tablediff is designed to compare 2 tables at time and will generate one .sql script file, and there is no way to do an automatic comparison on all tables between 2 databases with tablediff. To do that, I decided to create PowerShell script to run tablediff on all tables in database.

I use PowerShell version 3, with imported SQL Server 2012 SQLPS module. To demonstrate solution let’s create 2 databases with 2 tables (source and destination databases must have the same tables names).

CREATE DATABASE SourceDB;
GO
CREATE DATABASE DestinationDB;
GO
USE SourceDB;
GO
CREATE TABLE dbo.CompareTable (col1 int identity(1,1),col2 varchar(10))
GO
INSERT INTO dbo.CompareTable (col2)
SELECT 'test' UNION ALL
SELECT 'test1' UNION ALL
SELECT 'test2'
GO
CREATE TABLE dbo.CompareTable1 (col1 int identity(1,1),col2 varchar(10))
GO
INSERT INTO dbo.CompareTable1 (col2)
SELECT 'test' UNION ALL
SELECT 'test1' UNION ALL
SELECT 'test2'
GO
USE DestinationDB
GO
CREATE TABLE dbo.CompareTable(col1 int identity(1,1),col2 varchar(10))
GO
INSERT INTO dbo.CompareTable (col2)
SELECT 'test1' UNION ALL
SELECT 'test2'
GO
CREATE TABLE dbo.CompareTable1 (col1 int identity(1,1),col2 varchar(10))
GO
INSERT INTO dbo.CompareTable1 (col2)
SELECT 'test' UNION ALL
SELECT 'test1' UNION ALL
SELECT 'test2'
GO
SELECT * FROM SourceDB.dbo.CompareTable
SELECT * FROM SourceDB.dbo.CompareTable1
SELECT * FROM DestinationDB.dbo.CompareTable
SELECT * FROM DestinationDB.dbo.CompareTable1
GO
/*
SourceDB.dbo.CompareTable
col1        col2
----------- ----------
1           test
2           test1
3           test2
SourceDB.dbo.CompareTable1
col1        col2
----------- ----------
1           test
2           test1
3           test2
DestinationDB.dbo.CompareTable
col1        col2
----------- ----------
1           test1
2           test2
DestinationDB.dbo.CompareTable1
col1        col2
----------- ----------
1           test
2           test1
3           test2
*/

Now we have 2 databases with 2 tables and sample data. We see that data in CompareTable of SourceDB and DestinationDB aren’t the same.
To compare data in these tables with PowerShell we can run. (Before you run the command change the SourceServer and DestinationServer)

& "C:\Program Files\Microsoft SQL Server\110\COM\tablediff.exe" -SourceServer SERVER\SQL2012 -SourceDatabase SourceDB -sourcetable CompareTable -DestinationServer SERVER\SQL2012 -DestinationDatabase DestinationDB  -destinationtable CompareTable -c -f D:\1\Script

TableDiff

We see 3 differences from result and Script.sql file is created to fix differences.
Content of Script.sql is:

TableDiff_Script_sql

 That is simple example how to run tablediff to compare data in 2 tables and to generate .sql script to fix differences.

To compare data in all tables we need to loop through all tables in SourceDB and run tablediff for all tables. For every table we also need to set new file name for the T-SQL script file. (If file name specified in -f parameter exists tablediff will fail with error “The file %s already exists.”).

Here is the script:

# Date:     25/02/2013
# Author:   Ana Mihalj
# Description:  PS script to execute tablediff to compere and find all differences in data between 2 databases.
# Version:  1.0
# Example Execution: (with default parameters) .\TableDiffWithParam.ps1
# or with non-default parameters
# .\TableDiffWithParam.ps1 -SourceServer SERVER\INSTANCE -SourceDatabase SourceDB -DestinationServer SERVER\INSTANCE -DestinationDatabase DestinationDB -OutputFolder D:\Folder

param( [string]$SourceServer = "SERVER\INSTANCE",
[string]$SourceDatabase = "SourceDB",
[string]$DestinationServer = "SERVER\INSTANCE",
[string]$DestinationDatabase = "DestinationDB",
[string]$OutputFolder = "D:\Folder"
)
#set path to tablediff utility
$tablediff = "C:\Program Files\Microsoft SQL Server\110\COM\tablediff.exe"

#get tables for compare
if ($SourceServer.Contains("\") -eq "True")
{ $Tables = Get-ChildItem SQLSERVER:\SQL\$SourceServer\Databases\$SourceDatabase\Tables\ | SELECT name }
else
{ $Tables = Get-ChildItem SQLSERVER:\SQL\$SourceServer\Default\Databases\$SourceDatabase\Tables\ | SELECT name }

#create output folder if it does not exist
if ((Test-Path $OutputFolder) -eq $false)
{
md $OutputFolder
}

#Output file
$OutputFile = $OutputFolder+"\Output.txt"

#for each table
foreach($table in $Tables)
{
#create new file name for the Transact-SQL script file
$DiffScript = $OutputFolder+"\"+$table.Name+".sql"

#If file exists throw the errror.
if ((Test-Path $DiffScript) -eq $true)
{
throw "The file " + $DiffScript + " already exists."
}

#execute tablediff
& $tablediff -sourceserver $SourceServer -sourcedatabase $SourceDatabase -sourcetable $table.Name -destinationserver $DestinationServer -destinationdatabase $DestinationDatabase -destinationtable $table.Name -strict -c -o $OutputFile -f $DiffScript

# check the return value and throw an exception if needed
# tablediff return values: 0 - Success, 1 - Critical error, 2 - Table differences
if ($LastExitCode -eq 1)
{
throw "Error on table " + $table.Name + " with exit code $LastExitCode"
}
}

Before you run the script you need to change parameters value and the path to tablediff location (if your installation isn’t SQL Server 2012 on default installation folder).
I hope you’ll find this script useful.

SQL Server 2012: Page restore from the SSMS UI

13/01/2012 1 comment

In my last blog post I wrote about various ways to detect damaged pages in the database and how to perform page restores in SQL Server 2005/2008. Same apply to SQL Server 2012. SQL Server 2008 doesn’t have UI support for page restore but SQL Server 2012 Management Studio (SSMS) brings us a nice UI support for page restore. If you prefer to do page restores with UI here are the steps:

    1. In SSMS Object Explorer right click on database -> Tasks -> Restore then click on Page…PR2012_1

Restore Page window will be opened.

2. In Restore Page window, chosen database is selected (1) and Pages grid (2) is automatically populated with damaged pages from msdb.dbo.suspect_pages table (see picture below).

PR2012_2

From Restore Page window you can run DBCC CHECKDB (3) to find if there are more damaged pages in the database or you can add additional pages if you want using Add (4) button. You also need to set the location for the log backup (5). Backup sets part (6) shows all backups that will be used to bring data pages up to date with the current log file.

3. Final step is to click OK button and damaged pages are restored. Open-mouthed smile

SQL Server Page Restore

10/01/2012 2 comments

We all know how backup is important. I usually say that restore is more important than backup. We can create backup but if we don’t know how to restore it, or we cannot because it isn’t correct we have nothing. So always check your backups and restore it on different locations. In situation when you need to react quickly restore practice can save you a lot of times and nerves.

In this post I decided to show how to use page restore. Page restore isn’t something that has to be done on a regular basic, but every DBA should be familiar with technique and requirements in case page restore is needed.

Page restore requirements and limitations

Page restore is future available from SQL server 2005 version. Enterprise edition allows online page restore (restore of metadata pages require offline restore) while standard edition support only offline page restore.

Page restore is a nice future especially if you have a large database and a few damaged pages. As with all nice futures, page restore has some requirements and limitations. To be able to use page restore database must to be in full or bulk logged recovery model. Page restore must start with full, file or filegroup backup and there must be an unbroken chain of log backups up to the current log file. All log backups must be applied so that the page is brought up to date with the current log file.

Be aware that page restore cannot be used to restore: transaction log, full-text, allocation pages (GAM, SGAM, PFS, DIFF, ML), file header page (PageId =0 ), boot page (PageId =9).

How to identify damaged pages?

Damaged pages can be identified in multiple ways.

1. Sometimes, executing some queries on database can result in an error 824 (invalid page checksum error).

Msg 824, Level 24, State 2, Line 1

SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0xbf649245; actual: 0xbf648ec5). It occurred during a read of page (1:80) in database ID 9 at offset 0x000000000a0000 in file ‘D:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\CorruptDB.mdf’. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

Analyzing the error we can see that database with databaseid = 9 has damaged page 80 on file 1 (page 1:80).

2. Damaged pages can be identified with DBCC CHECKDB command. Example results:

Msg 8928, Level 16, State 1, Line 2
Object ID 2105058535, index ID 0, partition ID 72057594038779904, alloc unit ID 72057594039828480 (type In-row data): Page (1:80) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 2
Table error: Object ID 2105058535, index ID 0, partition ID 72057594038779904, alloc unit ID 72057594039828480 (type In-row data), page (1:80). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -4.
CHECKDB found 0 allocation errors and 2 consistency errors in table ‘CorruptTable’ (object ID 2105058535).
CHECKDB found 0 allocation errors and 2 consistency errors in database ‘CorruptDB’.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (CorruptDB).

3. dbo.suspect_pages table on msdb database. It contains one row per page that failed with an 823 error or an 824 error.

Firtst3

4. Check the SQL Server Error Log, and look for errors related to corrupted pages

How to perform page restore?

To perform page restore we need full backup containing un-damaged pages, differential and all transaction log backups to the current time. In the RESTORE DATABASE statement, we need to use the PAGE clause to list the page IDs of all of the pages to be restored. If we use offline page restore we first need to create backup log with NORECOVERY option. In this example, I will use online page restore.

--Restore database
RESTORE DATABASE CorruptDB PAGE = '1:80'
FROM DISK = 'D:\Backup\CorruptDB_full.bak'
WITH NORECOVERY
-- Restore log
RESTORE LOG CorruptDB
FROM DISK = 'D:\Backup\CorruptDB_log1.bak'
WITH NORECOVERY

/*
Processed 1 pages for database 'CorruptDB', file 'CorruptDB' on file 1.
RESTORE DATABASE ... FILE= successfully processed 1 pages in 0.023 seconds (0.339 MB/sec).
Processed 0 pages for database 'CorruptDB', file 'CorruptDB' on file 1.
RESTORE LOG successfully processed 0 pages in 0.002 seconds (0.000 MB/sec).
*/

In the example above, full backup with pageid = 80 is restored with option norecovery. Then we restored log with norecovery option. If we try to restore log with RECOVERY option without taking the last backup we would get an error:

Processed 0 pages for database ‘CorruptDB’, file ‘CorruptDB’ on file 1.
The roll forward start point is now at log sequence number (LSN) 43000000026100001. Additional roll forward past LSN 43000000042700001 is required to complete the restore sequence.
RESTORE LOG successfully processed 0 pages in 0.002 seconds (0.000 MB/sec).

You can query database and all data are available except damaged page. If we try to query data from table containing damaged page after we restored it with NORECOVERY option we will get an error:

ERROR:

Msg 829, Level 21, State 1, Line 1
Database ID 9, Page (1:80) is marked RestorePending, which may indicate disk corruption. To recover from this state, perform a restore.

To finalize page restore we need to take log backup and to restore it with RECOVERY option.

-- Backup DB log
BACKUP LOG CorruptDB
TO DISK = 'D:\Backup\CorruptDB_log2.bak'
-- Restore DB log with RECOVERY option
RESTORE LOG CorruptDB
FROM DISK = 'D:\Backup\CorruptDB_log2.bak'
WITH RECOVERY

Now we can query our table without error.

Offline page restore procedure is slightly different. With offline restore we first need to take log backup with NORECOVERY option. It will put database to restoring state. Then we need to restore full database using PAGE clause and NORECOVERY option. Then differential and all transaction log backup need to be applied with NORECOVERY option. Last transaction backup, taken with NORECOVERY option, should be restored with RECOVERY option.

SQL Server 2012: Detecting Sort Warnings with Extended Events (sort_warning event) – Part 2

07/12/2011 Leave a comment

In my last post, SQL Server 2012: Detecting Sort Warnings with Extended Events (sort_warning event) I showed how to use sort_warning event with sql_text action to detect sort warnings in database with statements sent to SQL server instance. We also saw that using sql_text action we know what stored procedure or batch are causing sort warnings. But sometimes that isn’t enough.

If we have a stored procedure or a batch with more statements that use sort operator we would like to know the information about the exact statement inside stored procedure or batch that caused sort_warning event. In SQL Server 2012 that can be achieved using tsql_stack or tsql_frame actions. Action tsql_stack is available in SQL Server 2008 (R2) while tsql_frame action is new in SQL Server 2012.

Below is description for tsql_stack and tsql_frame actions from metadata DMV:

In this post I will use tsql_frame action. To run all examples from this post we need TestSortWarnings database I used in my last post. This time I will create Extended Events session SortWarning with event_file target and it will capture sqlserver.sort_warning event with sqlserver.sql_text and sqlserver.tsql_frame actions. Event session is configured with a predicate on the sqlserver.database_id to track sort warnings only on TestSortWarnings database. Before you execute the script, you need to replace a value for database_id with a value returned by SELECT DB_ID(‘TestSortWarnings’) statement. Next script will create and start Event session SortWarning.

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='SortWarning')
DROP EVENT SESSION [MultipleDataFiles] ON SERVER;
-- Create Event Session SortWarning
CREATE EVENT SESSION SortWarning ON SERVER
ADD EVENT sqlserver.sort_warning(
ACTION(sqlserver.sql_text,sqlserver.tsql_frame)
WHERE (sqlserver.database_id=(7))) -- replace database_id
ADD TARGET package0.event_file(SET filename = N'C:\Log\SortWarning.xel',max_file_size=(100))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS)
GO
-- Start the Event Session
ALTER EVENT SESSION SortWarning
ON SERVER
STATE=START
GO

With the event session created and started we can run next script which will generate sort warning event.

USE TestSortWarnings
GO
DECLARE @T1 TABLE (Col1 int, Col2 varchar(8000))
-- Insert data to table variable
INSERT INTO @T1 SELECT * FROM dbo.TestTable
-- Return data sorted on Col2 column
SELECT * FROM @T1
ORDER BY Col2 ASC
GO

Let’s read the data from the extended events log file.

SELECT CAST(event_data AS XML) AS event_data
FROM sys.fn_xe_file_target_read_file('C:\Log\SortWarning*.xel',null,null,null)

Here is the result:

Sort_warning_type and sql_text are collected (1) and we see information collected with tsq_frame action (2). We can use sql_handle and offset information (offsetStart and offsetEnd) from tsql_frame action element and sys.dm_exec_sql_text DMF to return the specific statement which caused sort_warning. Here is the script to retrieve data we need:

SELECT
x.object_name AS event_name,
DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), vent_data.value('(event/@timestamp)[1]', 'datetime')) AS timestamp,
event_data.value('(event/data[@name="sort_warning_type"]/text)[1]', 'varchar(20)') AS sort_warning_type,
event_data.value('(event/action[@name="sql_text"]/value)[1]', 'varchar(max)') AS sql_text,
frame_data.value('./@level','int') AS frame_level,
OBJECT_NAME(st.objectid, st.dbid) AS objectname,
SUBSTRING(st.text, (frame_data.value('./@offsetStart','int')/2)+1,
((CASE frame_data.value('./@offsetEnd','int')
WHEN -1 THEN DATALENGTH(st.text)
ELSE frame_data.value('./@offsetEnd','int')
END - frame_data.value('./@offsetStart','int'))/2) + 1) AS sort_warning_statement
FROM (SELECT object_name, CAST(event_data AS XML) AS event_data
FROM sys.fn_xe_file_target_read_file('C:\Log\SortWarning*.xel',null,null,null)) x
CROSS APPLY x.event_data.nodes('event/action[@name="tsql_frame"]/value/frame') Frame(frame_data)
OUTER APPLY sys.dm_exec_sql_text(CONVERT(varbinary(max), frame_data.value('./@handle','varchar(max)'),1)) st

The results:

From the result we can see that frame_level for batch is 0 and objectname is NULL. Sort_warning_statement column shows us what specific statement from the batch is causing sort warning while sql_text column returns complete batch sent to a SQL server instance. It gives us all information about sort warnings to the statement level.

Now let’s execute 2 stored procedures dbo.Test and dbo.Test1.

EXEC dbo.test
GO
EXEC dbo.test1
GO

If we retrieve the event data from the event session target again we will see 3 new records.

The output from event_file target:

We see that frame_level for stored procedure is 1 and we have the name of stored procedure and exact statement inside the stored procedure which caused sort warning. That’s all we need.

Let’s see what will happened if we encounter stored procedure nesting. In the next script I will create and execute stored procedure dbo.test2 which calls dbo.test1 procedure.

CREATE PROCEDURE dbo.Test2
AS
SET NOCOUNT ONEXEC dbo.Test1
GO

EXEC dbo.Test2
GO

The output from event_file target:

We can see that even with stored procedure nesting we have all necessary information about sort warnings event. Frame_level column is 2 and it tells us the nesting level for the stored procedure responsible for sort warning event.

I like how much information we can get with SQL Server 2012 extended events. Open-mouthed smile In this post I tried to show how easily we can track sort warnings to the statement level in SQL Server 2012.

I hope that you have learned something new. 🙂

SQL Server 2012: Detecting Sort Warnings with Extended Events (sort_warning event)

05/12/2011 1 comment

Sort warnings events will rise when sort operation is performed in a query and it doesn’t fit into memory. In this case, SQL Server needs to spill the sort operation into tempdb which can result in a very slow physical operation especially if multiple passes to tempdb is needed for sorting the data. Ideally, there shouldn’t be any sort warnings on a server.

How to detect Sort Warnings in SQL Server 2008 (R2)?

In SQL Server 2008 (R2), sort warning event can be detected using SQL Profiler. SQL Server default trace has this event included by default. But using Sort Warning event in SQL Profiler we can’t capture what statements are causing these warnings because TextData column isn’t available.  Solomon Rutzky presented a way of finding statements by using profiler trace, trigger and DBCC INPUTBUFFER.

SQL Server 2008 (R2) doesn’t provide an event through Extended Events to track sort warnings. In SQL Server 2012 this will change.

How to detect Sort Warnings in SQL Server 2012 RC0?

In SQL Server 2012 RC0, the number of event has expanded to 616 and one of these new events is sort_warning event. Extended Events Sort_warning event can be used to detect what statements are causing sort warnings.  I will show how to use sort_warning event to detect sort warnings in database. Let’s create a new database with a simple table and stored procedures for testing.

-- Create TestSortWarnings database
CREATE DATABASE TestSortWarnings
GO
USE TestSortWarnings
GO
-- Create TestTable
CREATE TABLE dbo.TestTable (Col1 int IDENTITY(1,1), Col2 varchar(8000))
GO
-- Insert data into table
INSERT INTO dbo.TestTable (Col2)
SELECT REPLICATE('A','8000')
GO 200

CREATE PROCEDURE dbo.Test
AS
SET NOCOUNT ON

-- Declare table variable
DECLARE @T1 TABLE (Col1 int, Col2 varchar(8000))
-- Insert data to table variable
INSERT INTO @T1
SELECT * FROM dbo.TestTable

-- Return data sorted on Col2 column
SELECT * FROM @T1
ORDER BY Col2 ASC
GO

CREATE PROCEDURE dbo.Test1
AS
SET NOCOUNT ON

-- Declare table variable
DECLARE @T1 TABLE (Col1 int, Col2 varchar(8000))
-- Insert data to table variable
INSERT INTO @T1
SELECT * FROM dbo.TestTable
-- Return data sorted on Col2 column
SELECT * FROM @T1
ORDER BY Col2 ASC
OPTION (RECOMPILE)

-- Example with DINSTINCT
SELECT DISTINCT Col2 FROM @T1
-- Example with GROUP BY
SELECT Col2, COUNT(*) FROM @T1
GROUP BY Col2

Table TestTable has 2 columns, no indexes and has 200 rows. Two stored procedures will generate sort warnings during execution. I will use this database, table and stored procedures in all my examples.

Detecting Sort Warnings with Extended Events

SQL Server 2012 management studio (SSMS) has UI for creating and managing Extended Events and it will be much easier to use it. Great thing is that SSMS includes an event session data viewer for all targets except the ETW file target. If you want to learn more about it you can read a post “Introducing the Extended Events User Interface” written by SQL Team. Post is focused on the mechanisms for creating event sessions and displaying event session data.

In this post I will use T-SQL but everything from this post can be done with UI. Next script will create event session SortWarning with ring_buffer target. It will capture sqlserver.sort_warning event with sqlserver.sql_text action. Event session is configured with a predicate on the sqlserver.database_id to track sort warnings only on TestSortWarnings database. Before you execute the script, you need to replace a value for database_id with a value returned by SELECT DB_ID(‘TestSortWarnings’) statement.


CREATE EVENT SESSION SortWarning ON SERVER
ADD EVENT sqlserver.sort_warning(
  ACTION(sqlserver.sql_text)
  WHERE (sqlserver.database_id=(7))) -- Replace database_id
ADD TARGET package0.ring_buffer
GO

-- Start the Event Session
ALTER EVENT SESSION SortWarning
ON SERVER
STATE=START
GO

I will use prepared test data to simulate sort warnings. I will also include Actual Execution Plan to demonstrate new warnings information in it.

USE TestSortWarnings
GO
DECLARE @T1 TABLE (Col1 int, Col2 varchar(8000))
-- Insert data to table variable
INSERT INTO @T1 SELECT * FROM dbo.TestTable

-- Return data sorted on Col2 column
SELECT * FROM @T1
ORDER BY Col2 ASC
GO

If we look at Active Execution Plan we can see a warning on sort operator.

Let’s execute the stored procedure Test:

 EXEC dbo.Test
GO 

Let’s retrieve the event data from the event session target:

DECLARE @XEvent XML
SELECT @XEvent = CAST(target_data AS XML)
FROM sys.dm_xe_sessions s
INNER JOIN sys.dm_xe_session_targets t
  ON s.address = t.event_session_address
WHERE s.name = 'SortWarning'
AND t.target_name = 'ring_buffer'

SELECT
  event_data.value('(./@name)[1]', 'varchar(20)') AS event_name,
  DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), event_data.value('(./@timestamp)[1]', 'datetime')) AS timestamp,
  event_data.value('(./data[@name="sort_warning_type"]/text)[1]', 'varchar(20)') AS sort_warning_type,
  event_data.value('(./action[@name="sql_text"]/value)[1]', 'varchar(max)') AS sql_text
FROM @XEvent.nodes('RingBufferTarget[1]/event') XE(event_data)
 

Results:

Sort_warning event is raised 2 times. We see that singe pass over the data are required to sort the data and statements which caused spill to tempdb.  Let’s now execute stored procedure dbo.Test1 (with included Actual Execution plan).

 EXEC dbo.Test1
GO 

If we look at Actual Execution Plan we can see that stored procedure Test1 caused 2 sort warnings. (1) Second statement in stored procedure with ORDER BY didn’t cause sort warnings because of OPTION (RECOMPILE). Statements with DISTINCT (2) and GROUP BY (3) caused sort warnings.

If we retrieve the event data from the event session target again we will see 2 new records.

As we can see, by capturing only sqlserver.sort_warning event and sqlserver.sql_text action in event session we have information about the statement sent from a client to an SQL Server. Sometimes, that can be enough, but in some situations we need more information. If we have a stored procedure, which can be nested for example, with more statements that use sort operator (like Test1 stored procedure from our example), we will not know exactly which statements inside a stored procedure are causing sort warning events.

My next post will show how we can track sort warnings to the statement level inside a stored procedure.

 

SQL Server 2012: Service Accounts Changes

22/11/2011 Leave a comment

After SQL Server 2012 RC0 installation I noticed a few changes in SQL Server services account configuration. In previous versions (SQL Server 2005 and 2008/R2) during stand-alone SQL Server installation, local Windows group is created and service account for services or service SID is added as a member of the service group.

In SQL Server 2012 that is changed.  Local windows groups are created only for SQL Server Browser (SQLServer2005SQLBrowserUser$ComputerName), SSAS (SQLServerMSASUser$ComputerName$MSSQLSERVER) and PowerPivot for SharePoint (SQLServerMSASUser$ComputerName$PowerPivot).
Permissions and ACL for all other services (SQL Server Engine, SQL Server Agent, Full-text search…) are set to the per-service SIDs. 

Other interesting thing is number of created logins in the SQL Server Database Engine after SQL Server 2012 RC0 installation. Regarding the service SID for SQL Server Engine and SQL Server Agent there is no difference between SQL Server 2012 RC0 and SQL Server 2008. The service SID for SQL Server Engine and SQL Server Agent is added as a login to the sysadmin server role. 
In SQL Server 2012 RC0, logins are created for the service SID of the SQL Server VSS Writer (NT SERVICE\SQLWriter) and the Windows WMI provider (NT SERVICE\Winmgmt). These service SID logins are added to the sysadmin fixed server role. 

There are other changes and news. Windows 7 and Windows Server 2008 R2 have two new types of service accounts: managed service accounts (MSA) and virtual accounts.
I wrote a few changes I noticed after installation and after I read documentation.  It’s good to know what changes are done with SQL Server services accounts and I recommend that you read the BOL article: “Configure Windows Service Accounts and Permissions”.