SQLPFE.IL

Where is the Help in SQL 2012? F1

Oren Bouni — Sun, 28 Apr 2013 18:00:00 GMT

In SQL 2012 the BOL (Books Online) are only located on the internet. When you press F1, you are redirected to the web, so what can we do in a closed environment, where my production server is not connected to the internet and I need the F1?

Checking for help regarding the sys.dm_exec_cursors system table will redirect me to the MSDN (I need to be connected to the internet for that).

1) Download the BOL file from this link file name is (SQLServer2012Documentation_December2012_EN.exe):
    http://www.microsoft.com/en-gb/download/details.aspx?id=347
    after the file has been downloaded, we will extract it and send it to a folder (it can be a shared folder so
    more DBA will be able to upload it to their SSMS).

2) In SQL 2012 SSMS, on the help menu, we have the help settings (Ctrl+Alt+F1)
chose the Manage Help Settings

3) On the next screen, change the setting option to local in the {Choose online or local help} and press OK.

4) On the main screen, choose the {Install Content from Disk} the 4th option,
and on the "install content" choose the path where the BOL.exe extracts the information.

5) Look for the HelpContentSetup.msha file.
    By pressing the "next" button, it will show all the books online that will be upload to the local help.
    By pressing the "Add" button will add them to the SSMS help.
    By pressing the "upload" button, it will upload the BOL to the SSMS
    (you can also add other BOL in this way).

6) After the files are loaded we will get the final windows.

7) On the SSMS, pressing F1 will load the local help:

This is the way we can upload the BOL, so we access it locally.

SQL 2012 System health–fast analysis

Oren Bouni — Mon, 25 Mar 2013 06:23:00 GMT

Once we encounter a few problems with the SQL 2012 and we need to analyze it, what will be the first recommended step to start with?

During my last post I have discussed the default trace in SQL and its benefits.
SQL 2008 introduced a new option called extended events (which is also know as Xevents), it allows the DBA to get information regarding the SQL Instance. On the 2008 version, working with Xevent was complicated and clumsy.

It is important to understand the idea that Xevent will replace the good and old Profiler, so every DBA needs to know and work with Xevents.

Here is the MSDN quote on the future deprecation of SQL Server Profiler (and the Link).

"We are announcing the deprecation of SQL Server Profiler for Database Engine Trace Capture and Trace Replay. These features will be supported in the next version of SQL Server, but will be removed in a later version"

http://msdn.microsoft.com/en-gb/library/ms181091.aspx

SQL 2012 gives the Xevents a boost forward and simplified the way of working with it.

In SQL 2012, the DEV team added a kind of default Xevent trace called system_health*.xel The files are placed in the LOG folder.

Mostly when addressing an SQL server with performance issues and we'd like to do a fast checkup to get a direction on where the problem can be. We will be able to read this info with these files.

This XEL files can be viewed in 2 ways:

Windows rich client: double clicking with the mouse on the XEL file will open the SSMS (SQL Server Management Studio) showing the file information.
You can add columns, filters, aggregate the information and group it.
T-SQL, you can use the function sys.fn_xe_file_target_read_file
This function will get the file location as a parameter and will read the XML file.

Here are 2 DEMO of and XEL file T-SQL that returns information.

Spinlock and CPU utilization:

         declare @path_to_health_session nvarchar(1000) = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012NEW\MSSQL\Log\system_health_*.xel'

        select 

             TODATETIMEOFFSET ( T.sdnodes.value('(event/@timestamp)[1]','datetime'), '-06:00') as [timestamp],

             T.sdnodes.value('(event/data[@name="component"]/text)[1]', 'varchar(100)') as [component_name],

             T.sdnodes.value('(event/data[@name="state"]/text)[1]', 'varchar(100)') as [component_state],

             T.sdnodes.value('(event/data[@name="data"]/value/system[1]/@spinlockBackoffs)[1]', 'int') as [spinlockBackoffs],

             T.sdnodes.value('(event/data[@name="data"]/value/system[1]/@sickSpinlockTypeAfterAv)[1]', 'varchar(100)') as [sickSpinlockTypeAfterAv],

             T.sdnodes.value('(event/data[@name="data"]/value/system[1]/@isAccessViolationOccurred)[1]', 'int') as [isAccessViolationOccurred],

             T.sdnodes.value('(event/data[@name="data"]/value/system[1]/@writeAccessViolationCount)[1]', 'int') as [writeAccessViolationCount],

             T.sdnodes.value('(event/data[@name="data"]/value/system[1]/@intervalDumpRequests)[1]', 'int') as [intervalDumpRequests],

             T.sdnodes.value('(event/data[@name="data"]/value/system[1]/@nonYieldingTasksReported)[1]', 'int') as [nonYieldingTasksReported],

             T.sdnodes.value('(event/data[@name="data"]/value/system[1]/@pageFaults)[1]', 'bigint') as [pageFaults],

             T.sdnodes.value('(event/data[@name="data"]/value/system[1]/@systemCpuUtilization)[1]', 'int') as [systemCpuUtilization],

             T.sdnodes.value('(event/data[@name="data"]/value/system[1]/@sqlCpuUtilization)[1]', 'int') as [sqlCpuUtilization],

             T.sdnodes.value('(event/data[@name="data"]/value/system[1]/@BadPagesDetected)[1]', 'int') as [BadPagesDetected],

             T.sdnodes.value('(event/data[@name="data"]/value/system[1]/@BadPagesFixed)[1]', 'int') as [BadPagesFixed],

             T.sdnodes.value('(event/data[@name="data"]/value/system[1]/@LastBadPageAddress)[1]', 'nvarchar(30)') as [LastBadPageAddress],

             T.sdnodes.value('(event/data[@name="data"]/value/system[1]/@writeAccessViolationCount)[1]', 'int') as [writeAccessViolationCount]

        FROM

        (    SELECT bpr.query('.') as sdnodes

            FROM 

            (   select CAST(event_data AS XML)  as target_data,*

                from sys.fn_xe_file_target_read_file(@path_to_health_session,NULL,NULL,NULL)

                where object_name like 'sp_server_diagnostics_component_result'

            ) AS x

            CROSS APPLY target_data.nodes('/event') AS n(bpr)

        ) as T

        WHERE T.sdnodes.value('(event/data[@name="component"]/text)[1]', 'varchar(100)')='SYSTEM'

        order by TODATETIMEOFFSET ( T.sdnodes.value('(event/@timestamp)[1]','datetime'), '-06:00') asc

Resource memory:

          declare @path_to_health_session nvarchar(1000) = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012NEW\MSSQL\Log\system_health_*.xel'

          select 

               TODATETIMEOFFSET ( T.sdnodes.value('(event/@timestamp)[1]','datetime'), '-06:00') as [timestamp],

               T.sdnodes.value('(event/data[@name="component"]/text)[1]', 'varchar(100)') as [component_name],

               T.sdnodes.value('(event/data[@name="state"]/text)[1]', 'varchar(100)') as [component_state],

               T.sdnodes.value('(event/data[@name="data"]/value/resource/@lastNotification)[1]', 'varchar(200)') as [lastNotification],

               T.sdnodes.value('(event/data[@name="data"]/value/resource[1]/@outOfMemoryExceptions)[1]', 'int') as [outOfMemoryExceptions],

               T.sdnodes.value('(event/data[@name="data"]/value/resource/@isAnyPoolOutOfMemory)[1]', 'int') as [isAnyPoolOutOfMemory],

               T.sdnodes.value('(event/data[@name="data"]/value/resource/@processOutOfMemoryPeriod)[1]', 'int') as [processOutOfMemoryPeriod],

               T.sdnodes.value('(event/data[@name="data"]/value/resource/memoryReport[@name="Process/System Counts"]/entry[@description="Available Physical Memory"]/@value)[1]', 'bigint') as [Available Physical Memory],

               T.sdnodes.value('(event/data[@name="data"]/value/resource/memoryReport[@name="Process/System Counts"]/entry[@description="Available Virtual Memory"]/@value)[1]', 'bigint') as [Available Virtual Memory],

               T.sdnodes.value('(event/data[@name="data"]/value/resource/memoryReport[@name="Process/System Counts"]/entry[@description="Available Paging File"]/@value)[1]', 'bigint') as [Available Paging File],

               T.sdnodes.value('(event/data[@name="data"]/value/resource/memoryReport[@name="Process/System Counts"]/entry[@description="Working Set"]/@value)[1]', 'bigint') as [Working Set],

               T.sdnodes.value('(event/data[@name="data"]/value/resource/memoryReport[@name="Process/System Counts"]/entry[@description="Percent of Committed Memory in WS"]/@value)[1]', 'int') as [Percent of Committed Memory in WS],

               T.sdnodes.value('(event/data[@name="data"]/value/resource/memoryReport[@name="Process/System Counts"]/entry[@description="Page Faults"]/@value)[1]', 'bigint') as [Page Faults],

               T.sdnodes.value('(event/data[@name="data"]/value/resource/memoryReport[@name="Process/System Counts"]/entry[@description="System physical memory high"]/@value)[1]', 'int') as [System physical memory high],

               T.sdnodes.value('(event/data[@name="data"]/value/resource/memoryReport[@name="Process/System Counts"]/entry[@description="System physical memory low"]/@value)[1]', 'int') as [System physical memory low],

               T.sdnodes.value('(event/data[@name="data"]/value/resource/memoryReport[@name="Process/System Counts"]/entry[@description="Process physical memory low"]/@value)[1]', 'int') as [Process physical memory low],

               T.sdnodes.value('(event/data[@name="data"]/value/resource/memoryReport[@name="Process/System Counts"]/entry[@description="Process virtual memory low"]/@value)[1]', 'int') as [Process virtual memory low],

               T.sdnodes.value('(event/data[@name="data"]/value/resource/memoryReport[@name="Memory Manager"]/entry[@description="VM Reserved"]/@value)[1]', 'bigint') as [VM Reserved], 

               T.sdnodes.value('(event/data[@name="data"]/value/resource/memoryReport[@name="Memory Manager"]/entry[@description="VM Committed"]/@value)[1]', 'bigint') as [VM Committed],

               T.sdnodes.value('(event/data[@name="data"]/value/resource/memoryReport[@name="Memory Manager"]/entry[@description="Locked Pages Allocated"]/@value)[1]', 'bigint') as [Locked Pages Allocated],

               T.sdnodes.value('(event/data[@name="data"]/value/resource/memoryReport[@name="Memory Manager"]/entry[@description="Large Pages Allocated"]/@value)[1]', 'int') as [Large Pages Allocated],

               T.sdnodes.value('(event/data[@name="data"]/value/resource/memoryReport[@name="Memory Manager"]/entry[@description="Emergency Memory value"]/@value)[1]', 'int') as [Emergency Memory value],

               T.sdnodes.value('(event/data[@name="data"]/value/resource/memoryReport[@name="Memory Manager"]/entry[@description="Emergency Memory In Use"]/@value)[1]', 'int') as [Emergency Memory In Use],

               T.sdnodes.value('(event/data[@name="data"]/value/resource/memoryReport[@name="Memory Manager"]/entry[@description="Target Committed"]/@value)[1]', 'bigint') as [Target Committed],

               T.sdnodes.value('(event/data[@name="data"]/value/resource/memoryReport[@name="Memory Manager"]/entry[@description="Current Committed"]/@value)[1]', 'bigint') as [Current Committed],

               T.sdnodes.value('(event/data[@name="data"]/value/resource/memoryReport[@name="Memory Manager"]/entry[@description="Pages Allocated"]/@value)[1]', 'int') as [Pages Allocated],

               T.sdnodes.value('(event/data[@name="data"]/value/resource/memoryReport[@name="Memory Manager"]/entry[@description="Pages Reserved"]/@value)[1]', 'int') as [Pages Reserved],

               T.sdnodes.value('(event/data[@name="data"]/value/resource/memoryReport[@name="Memory Manager"]/entry[@description="Pages Free"]/@value)[1]', 'int') as [Pages Free],

               T.sdnodes.value('(event/data[@name="data"]/value/resource/memoryReport[@name="Memory Manager"]/entry[@description="Pages In Use"]/@value)[1]', 'int') as [Pages In Use],

               T.sdnodes.value('(event/data[@name="data"]/value/resource/memoryReport[@name="Memory Manager"]/entry[@description="Page Alloc Potential"]/@value)[1]', 'bigint') as [Page Alloc Potential],

               T.sdnodes.value('(event/data[@name="data"]/value/resource/memoryReport[@name="Memory Manager"]/entry[@description="NUMA Growth Phase"]/@value)[1]', 'int') as [NUMA Growth Phase],

               T.sdnodes.value('(event/data[@name="data"]/value/resource/memoryReport[@name="Memory Manager"]/entry[@description="Last OOM Factor"]/@value)[1]', 'int') as [Last OOM Factor],    

               T.sdnodes.value('(event/data[@name="data"]/value/resource/memoryReport[@name="Memory Manager"]/entry[@description="Last OS Error"]/@value)[1]', 'int') as    [Last OS Error]

          FROM

          (    SELECT bpr.query('.') as sdnodes

              FROM 

              (   select CAST(event_data AS XML)  as target_data,*

                  from sys.fn_xe_file_target_read_file(@path_to_health_session,NULL,NULL,NULL)

                  where object_name like 'sp_server_diagnostics_component_result'

              ) AS x

              CROSS APPLY target_data.nodes('/event') AS n(bpr)

          ) as T

          WHERE T.sdnodes.value('(event/data[@name="component"]/text)[1]', 'varchar(100)')='RESOURCE'

          order by TODATETIMEOFFSET ( T.sdnodes.value('(event/@timestamp)[1]','datetime'), '-06:00') asc

IO Subsystem:

          declare @path_to_health_session nvarchar(1000) = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012NEW\MSSQL\Log\system_health_*.xel'

          select 

               TODATETIMEOFFSET ( T.sdnodes.value('(event/@timestamp)[1]','datetime'), '-06:00') as [timestamp],

               T.sdnodes.value('(event/data[@name="component"]/text)[1]', 'varchar(100)') as [component_name],

               T.sdnodes.value('(event/data[@name="state"]/text)[1]', 'varchar(100)') as [component_state],

               T.sdnodes.value('(event/data[@name="data"]/value/ioSubsystem/@ioLatchTimeouts)[1]','int') as [ioLatchTimeouts],

               T.sdnodes.value('(event/data[@name="data"]/value/ioSubsystem/@intervalLongIos)[1]','int') as [intervalLongIos],

                T.sdnodes.value('(event/data[@name="data"]/value/ioSubsystem/@totalLongIos)[1]','int') as [totalLongIos],     

               T.sdnodes.value('(event/data[@name="data"]/value/ioSubsystem/longestPendingRequests/pendingRequest[1]/@duration)[1]','int') as [longestPendingRequests_duration],

               T.sdnodes.value('(event/data[@name="data"]/value/ioSubsystem/longestPendingRequests/pendingRequest[1]/@filePath)[1]','nvarchar(500)') as [longestPendingRequests_filePath],

               T.sdnodes.value('(event/data[@name="data"]/value/ioSubsystem/longestPendingRequests/pendingRequest[1]/@offset)[1]','int') as [longestPendingRequests_offset],

               T.sdnodes.value('(event/data[@name="data"]/value/ioSubsystem/longestPendingRequests/pendingRequest[1]/@handle)[1]','nvarchar(20)') as [longestPendingRequests_handle]

          FROM

          (    SELECT bpr.query('.') as sdnodes

              FROM 

              (   select CAST(event_data AS XML)  as target_data,*

                  from sys.fn_xe_file_target_read_file(@path_to_health_session,NULL,NULL,NULL)

                  where object_name like 'sp_server_diagnostics_component_result'

              ) AS x

              CROSS APPLY target_data.nodes('/event') AS n(bpr)

          ) as T

          WHERE T.sdnodes.value('(event/data[@name="component"]/text)[1]', 'varchar(100)')='IO_SUBSYSTEM'

So I encourage you to start and use the Xevents.

Trace flag at query level is supported

Stas Bogachinsky — Mon, 25 Feb 2013 21:10:47 GMT

Have you ever wanted to execute specific query with specific trace flag turned on, without affecting the session or the server level ?
If the answer is yes, now you have a hint to support it – "QUERYTRACEON".
It been out there for a while, but recently it was documented and is supported for use.
You are welcome to read about it here.

Check your SQL – default Trace

Oren Bouni — Mon, 21 Jan 2013 09:09:45 GMT

Every installation of SQL has a default trace. The default trace was introduced in SQL 2005, It is the same trace that we know from the profiler or server but it is a preconfigured trace that is enabled as default. The default trace create five *.TRC file that are been recycle when they arrive to 20 MB size.

The default trace provides troubleshooting option and assistance to DBA’s by providing a trace log with the necessary data to diagnose problems that occur for the first time.

The default trace can be viewed by the profiler tool or the SQL function fn_trace_gettable.

My Default Trace

How do I know the status of the trace file or how to configure it?

I have a few things to set up:

The trace file
Stop and start the trace
My default trace
Log file PATH

let's begin.

1) SYS.CONFIGURATION system table, this system TAB contain all configuration option and their status. By running
this Query I can get the required info:

  1: SELECT * FROM  sys.configurations WHERE  configuration_id= 1568;
  2: -- Value_in_use  = the status of the option 
  3: -- is_dynamic  = if the configured value need a service restarted.

2) Using the SP_CONFIGURE will help to configure and change the status of the default trace

  1: sp_configure'default trace enabled',1
  2: Reconfigure

3) The system table sys.traces shows the status of the trace, path of the file, size and other information.

  1: Select * from sys.traces

The default trace file can help us investigating information regarding database events, audit events, security information error and warnings, full text events, Server Memory Change Events and much more.

In this example I’m using the a parameter to collect the last TRC file with

  1: SELECT TOP 1 value FROM sys.fn_trace_getinfo(NULL) WHERE property= 2

After identifying the correct path and the name of the recent log, we can use it here:

  1: Declare@LogPathsql_variant
  2: SET@logPath=(Selecttop 1 valueFROMsys.fn_trace_getinfo(NULL) WHERE property= 2)
  3: 
  4: SELECT  tg.TextData,tg.DatabaseName,tg.Error,tg.ObjectName,tg.DatabaseName,te.name,
  5:     tg.EventSubClass,tg.NTUserName,tg.NTDomainName,tg.HostName,tg.ApplicationName,tg.Spid,
  6:     tg.Duration,tg.StartTime,tg.EndTime,tg.Reads,tg.Writes,tg.CPU
  7: FROM  fn_trace_gettable(cast(@LogPathasvarchar(250)),default)AStg
  8:     Innerjoinsys.trace_eventsasteontg.EventClass=te.trace_event_id
  9: Where  tg.DatabaseName<>'tempdb'
 10: ---------------------------------------------------------------------------------------
 11: --- WHERE   te.name = 'Data File Auto Grow' 
 12: OR te.name = 'Data File Auto Shrink' = Database events
 13: ---------------------------------------------------------------------------------------
 14: --- WHERE   te.name = 'Missing Column Statistics' 
 15: OR te.name = 'Missing Join Predicate' = Errors and warnings events
 16: ---------------------------------------------------------------------------------------
 17: --- WHERE  te.name IN ('Audit Addlogin Event', 'Audit Add DB User Event', 
 18: 'Audit Add Member to DB Role Event') = Security and Audit events
 19: ---------------------------------------------------------------------------------------
 20: --- WHERE   te.name IN ('Server Memory Change') = Memory events
 21: ---------------------------------------------------------------------------------------
 22: --- WHERE  te.name IN ('Object Altered','Object Created',
 23: 'Object Deleted') = Object Events
 24: ---------------------------------------------------------------------------------------
 25: --- WHERE  te.name IN ('FT Crawl Aborted','FT Crawl Started',
 26: 'FT Crawl Stopped') = Full Text events
 27:

Bottom line

Default trace is a great and powerful way to check the health, the status and the security of the SQL Server instance. It is important to be aware of it existence and use it when necessary.
I urge you not to switch it off the default server trace, some of the build-in reports that we have on the SSMS are collecting information from the default trace file.
I heard some people saying it might cost you in performance. If you suspect the default trace to be causing you an overhead, I recommend checking the "wait type" dynamic management view (DMV) in order confirm your suspicion. (sys.dm_os_wait_stats), in this DMV look for high values in this SQLTRACE_INCREMENTAL_FLUSH_SLEEP, SQLTRACE_WAIT_ENTRIES

Each time I examine the TRC files I manage to find more and more info about my databases,
this feature has lots of benefits and I found it quite useful.
Trace file are being deprecated these days and in future version of SQL we will use X-Event known more as extended events.
I will write a post on extended events in SQL 2012 in one of the next posts

How to avoid text-to-date conversion mismatches

Stas Bogachinsky — Mon, 14 Jan 2013 11:40:11 GMT

In one of our previous posts we saw how different set options can affect execution plans and result sets.
Now let's see how to write our code in order to avoid text-to-date conversion mismatches.

In this example we can see one string being converted to two different dates:

set language us_english
select cast('11/12/2012' as datetime)

Result: 2012-11-12 00:00:00.000 -> November 12th 2012

set language polski
select cast('11/12/2012' as datetime)

Result: 2012-12-11 00:00:00.000 -> December 11th 2012

The difference is caused by default dateformat for different languages.
The same problem happens for all date data types: smalldatetime, datetime, date, datetime2, datetimeoffset.

The only way to make sure casting is culture independent is to supply date string in one of following formats:

YYYYMMDD without forward or backward slashes or dashes (/ \ -) with or without time
example:

set language us_english
select cast('20121112' as datetime)
Result: 2012-11-12 00:00:00.000 -> November 12th 2012

set language polski
select cast('20121112' as datetime)
Result: 2012-11-12 00:00:00.000 -> November 12th 2012

YYYY-MM-DDThh:mi:ss without spaces
example:

set language us_english
select cast('2012-11-12T00:00:00' as datetime)
Result: 2012-11-12 00:00:00.000 -> November 12th 2012

set language polski
select cast('2012-11-12T00:00:00' as datetime)
Result: 2012-11-12 00:00:00.000 -> November 12th 2012

My personal favorite is the first one as it look much more readable and doesn't have to include time.

the case of the unexplained - how we can get different execution plans and result sets for same query

Stas Bogachinsky — Wed, 12 Dec 2012 11:12:00 GMT

The other day, my colleague and a good friend called me and told that her SQL Server gone nuts.
One of developers launched a query and it run for ages, once she executed the same query it returned results in a few seconds.
So how something like this can happen ?

Each compilation is done for specific set of server, database, user, connection and set options.
The most common cause in a difference between execution plans for same query, is set option "ARITHABORT". In most connection string builders it is "off" by default. In SQL Server Management Studio it's "on" by default.
Once we get problematic query we try to troubleshot it within Management Studio and might get different execution plan.

So ARITHABORT was my immediate suspect and I asked my colleague to check it.
We can compare set options of different connections by querying sys.dm_exec_sessions DMV.
Surprisingly it wasn't the case, however DMV showed difference between my colleague and application login languages - us_english and polski.

At this stage I was almost sure that execution plan was not the only thing that was different.
As a result of language difference and usage of "dependent" date format the resultset was different too, as SQL Server implicitly converted date string like "07/10/2012" to different dates October 7th and July 10th.

We'll talk about "dependent" and "independent" date formats in one of our future posts.

As you can see, the execution plan can be influenced by different factors and it is important to be aware of this.
You are welcome to explore sys.dm_exec_sessions DMV and sys.dm_exec_plan_attributes function. (http://msdn.microsoft.com/en-us/library/ms176013.aspx, http://msdn.microsoft.com/en-us/library/ms189472.aspx)

Working with SQLIO and analyzing it’s output

Oren Bouni — Tue, 04 Dec 2012 08:53:00 GMT

Introduction

While working on a customer request, I faced the question what is the SQLIO? how to operate it and how to analyze its outputs?

The customer has read about the tool and was also able to run it but wanted more information on how to do it and how to easily analyze the SQLIO output.

What is SQLIO

SQLIOis a Disk Subsystem Benchmark Tool, provided by Microsoft, used to determine the I/O capacity of a given configuration. The SQLIO is a tool that coms “as is” no support is given.

SQLIO is been use to determent the best IO configuration for a certain load.

Working with SQLIO

Before you start working with the tool there are some preparations that need to be done.

- Parameter file
The Parameter file contains the info of the server and the path of the test file:

## File Path = the test file location. In this file we will have all the test requests
## 2 = Number of test file has number of CPU – when testing multiple
Path reduce the file number or split the tests.
## 0x0 = Affinity mask to bind operations against this file to particular CPU's.
Use always the 0x0 musk setting.
## 100 = File size in MB recommend to be 2-4 time bigger than the SAN cache.
----------------------------------------------------------------------------------------
File stretcher
c:\testfile.dat20x0100
d:\testfile.dat20x0100

- Test file
The test file will have the test details that we would like to issue on the server;
the reads are sequential or random, read or write, size of the read or write, output file etc.

Test large random read, outstanding I/O requests 8

callsqlio -kR-s60 –frandom –o8 –b64 -LS -Fparam.txt

Test large sequential write, outstanding I/O requests 32

callsqlio -kW-s60 –fsequential –o32 –b8 –LS -Fparam.txt

Option	Description	Parameter
-o	Number of outstanding I/O requests per thread. When attempting to determine the capacity of a given volume or set of volumes, start with a reasonable number for this and increase until disk saturation is reached.	-o8 –o32, -o64 ….
-LS	Instructs SQLIO to capture disk latency information.	-LS
-k	Specify either R or W (read or write). Both reads and writes should be tested.	-kR, -kW
-s	Duration of test (in seconds). For initial tests, running for 5-10 Min per IO.	-s60, -s120
-b	Random I/O, pay close attention to the number of I/Os per second and latency. For sequential I/O, test a range of sizes (4, 8, 16, 32, 64, 128,256) and pay attention to throughput and latency.	-b8, -b32, -b64
-f	Type of I/O to issue. Either 'random' or 'sequential'	-frandom, -fsequential
-F	Name of the file that will contain a list of the test file(s) used by SQLIO.	-Fparam.txt

The output

One of the downside of the SQLIO is reading the output of the test.
Now after running the SQLIO script we are getting the output and we need to analyze it.
Lots of details and it repeat itself; The output is in text format and look exactly like this:
(Attached is a result demo file)

C:\Windows\system32>"c:\Program Files (x86)\SQLIO\sqlio.exe" -kW -t2 -s120 -o4 -frandom -b64 -BH -LS -FJ:\SQLIO\param_g.txt
sqlio v1.5.SG
using system counter for latency timings, 2081142 counts per second
parameter file used: J:\SQLIO\param_g.txt
file g:\testfile.dat with 2 threads (0-1) using mask 0x0 (0)
2 threads writing for 120 secs to file g:\testfile.dat
using 64KB random IOs
enabling multiple I/Os per thread with 4 outstanding
buffering set to use hardware disk cache (but not file cache)
using specified size: 20000 MB for file: g:\testfile.dat
initialization done
CUMULATIVE DATA:throughput metrics:
IOs/sec: 4419.34
MBs/sec: 276.20
latency metrics:
Min_Latency(ms): 0
Avg_Latency(ms): 1
Max_Latency(ms): 60
histogram:
ms: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+
%: 185 12 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

We need to read it and analyze it. PowerShell can help us.

When I needed to analyze this file, I looked up and I have found the PowerShell script in one of JonathanKehayias post, I took the basic Power tweak it a bit and added the option of generation the output to an excel power pivot table.

Excel Row Data

Excel Power Pivot table And Table with Graph

The PowerShell Script:

Download a SQLIO result demo file.

$sourceFile="C:\1\results_G.TXT"
$targetFile="C:\1\SLQIO_Pivot.xlsx"
$filedata=Get-Content$sourceFile|Out-String
$Results=$filedata.Split([string[]]"sqlio v1.5.SG",[StringSplitOptions]::RemoveEmptyEntries ) | `
select @{Name="Threads"; Expression={[int]([regex]::Match($_,"(\d+)?\sthreads\s(reading|writing)").Groups[1].Value)}},`
                           @{Name="Operation"; Expression={switch ([regex]::Match($_,"(\d+)?\sthreads\s(reading|writing)").Groups[2].Value){"reading" {"Read"}"writing" {"Write"}}}},`
                           @{Name="Duration"; Expression={[int]([regex]::Match($_,"for\s(\d+)?\ssecs").Groups[1].Value)}},`
                           @{Name="IOSize"; Expression={[int]([regex]::Match($_,"\tusing\s(\d+)?KB\s(sequential|random)").Groups[1].Value)}},`
                           @{Name="IOType"; Expression={switch ([regex]::Match($_,"\tusing\s(\d+)?KB\s(sequential|random)").Groups[2].Value){"random" {"Random"}"sequential" {"Sequential"}}}},`
                           @{Name="PendingIO"; Expression={[int]([regex]::Match($_,"with\s(\d+)?\soutstanding").Groups[1].Value)}},`
                           @{Name="FileSize"; Expression={[int]([regex]::Match($_,"\s(\d+)?\sMB\sfor\sfile").Groups[1].Value)}},`
@{Name="IOPS"; Expression={[decimal]([regex]::Match($_,"IOs\/sec\:\s+(\d+\.\d+)?").Groups[1].Value)}},`
@{Name="MBs_Sec"; Expression={[decimal]([regex]::Match($_,"MBs\/sec\:\s+(\d+\.\d+)?").Groups[1].Value)}},`
                           @{Name="MinLat_ms"; Expression={[int]([regex]::Match($_,"Min.{0,}?\:\s(\d+)?").Groups[1].Value)}},`
                           @{Name="AvgLat_ms"; Expression={[int]([regex]::Match($_,"Avg.{0,}?\:\s(\d+)?").Groups[1].Value)}},`
                           @{Name="MaxLat_ms"; Expression={[int]([regex]::Match($_,"Max.{0,}?\:\s(\d+)?").Groups[1].Value)}}`
|Sort-ObjectIOSize,IOType,Operation,Threads


####--------------  Setting the culture and UI culture incase not using en-US. -- See http://msdn.microsoft.com/en-us/library/office/aa168494 for more details
$currentThread=[System.Threading.Thread]::CurrentThread
$culture=[System.Globalization.CultureInfo]::GetCultureInfo("en-US")
$currentThread.CurrentCulture=$culture
$currentThread.CurrentUICulture=$culture
Set-Culture$culture


####--------------  Creatingth EXCEL Data Workbook and Sheet
$Excel=New-Object-ComObjectExcel.Application
$Excel.Visible=$true
$WorkBook=$Excel.WorkBooks.Add()
$WorkBook.WorkSheets.Item(1).Name ="RawData"
$WorkBook.WorkSheets.Item(2).Name ="Pivot"
$WorkBook.WorkSheets.Item(3).Delete()


####--------------  Now working on the Sheet rowdata and populating it
$WorkSheet=$WorkBook.WorkSheets.Item("RawData")
$x=2
$WorkSheet.Cells.Item(1,1) ="Threads"
$WorkSheet.Cells.Item(1,2) ="Operation"
$WorkSheet.Cells.Item(1,3) ="Duration"
$WorkSheet.Cells.Item(1,4) ="IOSize"
$WorkSheet.Cells.Item(1,5) ="IOType"
$WorkSheet.Cells.Item(1,6) ="PendingIO"
$WorkSheet.Cells.Item(1,7) ="FileSize"
$WorkSheet.Cells.Item(1,8) ="IOPS"
$WorkSheet.Cells.Item(1,9) ="MBs/Sec"
$WorkSheet.Cells.Item(1,10) ="Min_Lat(ms)"
$WorkSheet.Cells.Item(1,11) ="Avg_Lat(ms)"
$WorkSheet.Cells.Item(1,12) ="Max_Lat(ms)"
$WorkSheet.Cells.Item(1,13) ="Caption"
##$c=$worksheet.Activate(columns).count


$Results|% {
$WorkSheet.Cells.Item($x,1) =$_.Threads
$WorkSheet.Cells.Item($x,2) =$_.Operation
$WorkSheet.Cells.Item($x,3) =$_.Duration
$WorkSheet.Cells.Item($x,4) =$_.IOSize
$WorkSheet.Cells.Item($x,5) =$_.IOType
$WorkSheet.Cells.Item($x,6) =$_.PendingIO
$WorkSheet.Cells.Item($x,7) =$_.FileSize
$WorkSheet.Cells.Item($x,8) =$_.IOPS
$WorkSheet.Cells.Item($x,9) =$_.MBs_Sec
$WorkSheet.Cells.Item($x,10) =$_.MinLat_ms
$WorkSheet.Cells.Item($x,11) =$_.AvgLat_ms
$WorkSheet.Cells.Item($x,12) =$_.MaxLat_ms
$WorkSheet.Cells.Item($x,13) =[string]$_.IOSize+"KB "+[string]$_.IOType+" "+ `
[string]$_.Operation +" "+[string]$_.Threads + `
" Threads "+[string]$_.PendingIO+" pending"
$x++}


####--------------  Pivoting

$xlDatabase=1
$xlPivotTableVersion=3
$xlDataRange="RawData!R1C1:R{0}C13"-f ($x-1)
$PivotTable=$Workbook.PivotCaches().Create($xlDatabase,$xlDataRange,$xlPivotTableVersion)
[void]$PivotTable.CreatePivotTable("Pivot!R1C1")


####--------------  Save and close Excel
$Workbook.SaveAs($targetFile)
$Excel.Quit()
while([System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)){'.'}

Now go and start running the SQLIO (Do not do that in Prod environment)

SQL Server 2012 new feature The Snippets

Oren Bouni — Sun, 11 Nov 2012 11:56:00 GMT

Snippets

In SQL 2012 we have a new feature called snippets; So what are this snippets?

Lots of DBAs, when required to create a new T-SQL command, check out the syntax in BOL, they try to remember on how to do it, Or sometimes we need a repeating T-SQL Command like: Select, Proc, Func, triggers, tables etc. that we need to recreate with parametrical chances.

For that purpose, we have now in SQL 2012 the snippets. This feature can potentially help us with reducing development time and assist the productivity for developers and DBA’s.

Snippet are templates in SQL Server 2012 that are based on XML with predefined fields and values.

The snippets is not a server feature but an SSMS feature.

Snippets are categorized by groups in order to facilitate the use of them.

There are the three category of snippets in SQL 2012

DEFAULT SNIPPETS	SURROUND SNIPPETS	CUSTOM SNPPETS
Code templates for various T-SQL commands. We can quickly insert into our T-SQL code when creating tables, stored procedures, triggers, etc.	Code templates that allow users to implement code construct like Begin End, If, While, for, case, etc.	Custom Snippets enabling the user to create his own Snippet that can appear in the Snippet menu when called.

Snippets are usually stored in this path:

[C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\SQL\Snippets\1033]

Creating our first Snippets

Step #1 - Create a Snippet using XML:

In step1 we are creating the XML file that holds all the information about the template and the code of the template that we will use. In this example we will create a "create table" T-SQL command, the XML will contain all the information on our snippets.

  1: <?xmlversion="1.0"encoding="utf-8" ?>
  2: <CodeSnippetsxmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">
  3: <CodeSnippetFormat="1.0.0">
  4: <Header>
  5: <Title>TableTemplate</Title>
  6: <Description>insertthecolumnsnamesanddatatype.</Description>
  7: <Author>OrenBouni(MicrosoftCorporation-SQLPFE)</Author>
  8: <SnippetTypes>
  9: <SnippetType>Expansion</SnippetType>
 10: </SnippetTypes>
 11: </Header>
 12: <Snippet>
 13: <CodeLanguage="SQL">
 14: <![CDATA[
 15: Create Table Customers
 16: (
 17:   rgistrationDate  datetime default getdate(),
 18:   '<Col_Name>'  '<DataType>',
 19:   '<Col_Name>'  '<DataType>',
 20:   '<Col_Name>'  '<DataType>',
 21:   '<Col_Name>'  '<DataType>',
 22:   OfficeAddress  Varchar(100),
 23:   OfficeManager  Varchar(50)
 24: ) 
 25: Go
 26: ]]>
 27: </Code>
 28: </Snippet>
 29: </CodeSnippet>
 30: </CodeSnippets>

Step #2–register the snippets in the SSMS:

Once we have created the XML snippets file (as demonstrated above) we need to register the snippet in the SSMS, we can do one of the following:

Adding the snippets:

1. Create a new folder or use the existing folder and store the XML snippet in it, In my case I’ll use the new folder option and create a folder named Oren snippets

2. start SQL Server Management Studio

3. Select the “Tools” option from the menu items and Click on “Code Snippets Manager”

4. Click on the “Add” button

5. Browse to the folder containing the file, and click on the Select Folder button.

To Import a Snippet:

1. Launch SQL Server Management Studio

2. Select “Tools” from the menu items and click on “Code Snippets Manager”

3. Click on the “Import” button at the bottom.

4. Browse to the folder containing AddTable_Cust.snippet file

5. mark it, store it in the appropriate location I saved it in My Code Snippets and press OK.

After doing so, creating the snippet, adding it to the SSMS by importing it to the correct location, we can invoke the Snippet and use it in the SSMS.

To call a snippet we need in the SSMS new query windows to press the CTRL + K + X and then we will get the Snippet menu.

Now we will navigate to my code folder and choose the "create table" snippet,

Have fun and start using this nice and very helpful option.

PERFMON & SQL Server - a small useful tip

Oren Bouni — Sun, 04 Nov 2012 20:39:00 GMT

One of the best Performance tools that I use is the PERFMON.

PERFMON is a tool that exists out of the box on every windows machine and has lots of counters for almost every application. Perfmon provides current, accurate information on thousands of different attributes of your system.

If you open the PERFMON in windows [using the PERFMON key word in the run command line] the tool will open and will show the default counter %Processor time.

By clicking on the green + sign one can add new counters such as SQL, network, disk and other counters.

Now imagine you are in a middle of a performance crisis and we need to add the SQL counters.

Basically, this might take some time. In windows 2008, Vista and above it doesn't have to be song long; we can create customized template (configfile) of performance counters and launch it immediately.

To do so we need to run the perfmon in sys mode:

Doing so, will open the app in sys mode and in this screenshot we can add the counters we’d like to see every time we are facing a performance issue and we need to launch the perfmon rather quickly.

The perfmon is up and running with the new counters already, we can show it in the line format as seen here or in report / histogram format.

After that we need to click the file menu and save the perfmon file on the desktop or in a known folder.

The file will be save in the format of Performance Monitor Configuration [File_Name.PerfmonCfg]

The icon on the desktop will look like that

Now we can create some perfmon templates and launch it immediately with no need to preconfigure.

Stop using TOP to page for data from now on use the OFFSET

Oren Bouni — Mon, 29 Oct 2012 20:54:24 GMT

With all the innovation that SQL 2012 bring to us in the T-SQL area we have the offset command.

What is the offset command?

I early version of SQL (before 2012) when we wanted to select out of a table a number of row we all have used the TOP command, the top command is selecting the number applied by the command without the option of defining what to select.

This Query will select the top 10 row that are existing in the where condition.

  1: Select  top 10  BusinessEntityID, SalesQuota, CommissionPct, ModifiedDate
  2: From  Sales.SalesPerson
  3: Where  SalesQuota is not NULL

Yes we can try and do some manipulation of Where or Order by to select our data but it had its own limitation.

SQL 2012 bring us the offset option, this command help us to select the number of row we’d like to select and the offset of the row meaning from where it will start and bring us the row that we asked for.

In the next scrip we will create a Demo table populate it with data and select.

  1: -- Create the DEMO DB and use this DB for the demo
  2: Create database Demo
  3: go
  4: use demo
  5: go
  6: -----------------------------------------------------------------
  7: -- Create the table
  8: CREATE table sales (
  9:   salesID INT IDENTITY,
 10:   customerID INT DEFAULT CONVERT (INT, 100000 * RAND ()),
 11:   salesDate DATETIME , --DEFAULT GETDATE (),
 12:   salesAmount MONEY);
 13: go
 14: -----------------------------------------------------------------
 15: -- populate the table with data
 16: DECLARE @count INT
 17: SELECT @count = 0
 18: WHILE (@count < 5000)
 19: BEGIN
 20:   INSERT INTO sales (salesDate, salesAmount)
 21:     VALUES (getdate()-@count, 100 * RAND ());
 22:   SELECT @count = @count + 1
 23: END;
 24: GO

After creating the script we can run this 2 select, one with the top option and the other with the offset.

In the top option we have a sub select to help us retrieve the correct rowset to select from.

In the second select we are retrieving the data using the offset command it is essayer and shorter to do so with it.

Script with the top:

  1: select  top 3 salesid, customerID, salesDate, salesAmount
  2: from  sales
  3: where  salesAmount > 25
  4:     and salesAmount not in (  select top 6 salesAmount 
  5:             from sales 
  6:             order by salesAmount desc)
  7: order by salesAmount desc

Script with the offset:

  1: Select salesid, customerID, salesDate, salesAmount 
  2: from sales 
  3: where salesAmount > 25
  4: order by salesAmount desc
  5: offset 6 rows
  6: fetch next 3 rows only