View overall I/O statistics for all databases in your server

A sql script to find the I/O statistics for all databases on the server. This can be helpful for

"drafting a server consolidation strategy, or pointing to eventual need to check perf counters if I/O is waiting more than expected"

- From "Blog do Ezequiel" http://blogs.msdn.com/b/blogdoezequiel/archive/2012/03/08/the-sql-swiss-army-knife-3-view-i-o-per-file-updated.aspx

Code Snippet

-- 2012-03-08 Pedro Lopes (Microsoft) pedro.lopes@microsoft.com (http://blogs.msdn.com/b/blogdoezequiel/)
--
-- Checks the cumulative IO per database file and related information
--
SELECT f.database_id, DB_NAME(f.database_id) AS database_name, f.name AS logical_file_name, f.[file_id], f.type_desc,
    CAST (CASE
        -- Handle UNC paths (e.g. '\\fileserver\readonlydbs\dept_dw.ndf')
        WHEN LEFT (LTRIM (f.physical_name), 2) = '\\'
            THEN LEFT (LTRIM (f.physical_name),CHARINDEX('\',LTRIM(f.physical_name),CHARINDEX('\',LTRIM(f.physical_name), 3) + 1) - 1)
            -- Handle local paths (e.g. 'C:\Program Files\...\master.mdf')
            WHEN CHARINDEX('\', LTRIM(f.physical_name), 3) > 0
            THEN UPPER(LEFT(LTRIM(f.physical_name), CHARINDEX ('\', LTRIM(f.physical_name), 3) - 1))
        ELSE f.physical_name
    END AS NVARCHAR(255)) AS logical_disk,
    fs.size_on_disk_bytes/1024/1024 AS size_on_disk_Mbytes,
    fs.num_of_reads, fs.num_of_writes,
    fs.num_of_bytes_read/1024/1024 AS num_of_Mbytes_read,
    fs.num_of_bytes_written/1024/1024 AS num_of_Mbytes_written,
    fs.io_stall/1000/60 AS io_stall_min,
    fs.io_stall_read_ms/1000/60 AS io_stall_read_min,
    fs.io_stall_write_ms/1000/60 AS io_stall_write_min,
    ((fs.io_stall_read_ms/1000/60)*100)/(CASE WHEN fs.io_stall/1000/60 = 0 THEN 1 ELSE fs.io_stall/1000/60 END) AS io_stall_read_pct,
    ((fs.io_stall_write_ms/1000/60)*100)/(CASE WHEN fs.io_stall/1000/60 = 0 THEN 1 ELSE fs.io_stall/1000/60 END) AS io_stall_write_pct,
    ABS((sample_ms/1000)/60/60) AS 'sample_HH',
    ((fs.io_stall/1000/60)*100)/(ABS((sample_ms/1000)/60))AS 'io_stall_pct_of_overall_sample'
FROM sys.dm_io_virtual_file_stats (default, default) AS fs
INNER JOIN sys.master_files AS f ON fs.database_id = f.database_id AND fs.[file_id] = f.[file_id]
ORDER BY 18 DESC

SQL-CLR: User Defined TVF - Barcode Parser

So, I created another SQL-CLR. This time it is a User Defined Function that is used to parse barcodes.

When setting this up, I added two attributes

• FillRowMethodName - which is used to populate a table's row. The setup for this function, line 132 - 156, must accept an object as it's first value and then output fields that are used to populate the row.
• TableDefinition - what the table that I want to return should include. One of the problems I ran into was that I was originally using varchar as my table values, but I would get this error: "Cannot use 'varchar' column in the result table of a streaming user-defined function".

For the intializing function, BarCodeParsercs, I need to return an IEnumerable value. Each value that is return in this function will end up as a row. In my case I just want one row, so I just created a string array with my single value.

This will end up calling the FillRow function to populate the row, for each value in the IEnumerable. In this case I call the GetData function to parse out my barcode and return a string array of values I needed to populate each of the table fields.

Now to deploy, you will need to first set your database table as TRUSTWORTHY

Note: To help debug the deployment script, copy the auto-generated deployment sql script, which can be found in the \bin\Debug and run it in SSMS, turned on "SQLCMD Mode" and executed the statement.

Code Snippet

1. using System;
2. using System.Data;
3. using System.Data.SqlClient;
4. using System.Data.SqlTypes;
5. using Microsoft.SqlServer.Server;
6. using System.Collections;
7.  
8. public partial class UserDefinedFunctions
9. {
10.     [Microsoft.SqlServer.Server.SqlFunction(
11.         FillRowMethodName="FillRow",
12.         TableDefinition = "BarcodeType nvarchar(50) NULL, " +
13.                         "PO nvarchar(50) NULL, " +
14.                         "PartNumber nvarchar(50) NULL, " +
15.                         "Qty nvarchar(50) NULL, " +
16.                         "DateCode nvarchar(50) NULL, " +
17.                         "SupplierName nvarchar(50) NULL, " +
18.                         "SupplierSite nvarchar(50) NULL, " +
19.                         "Date nvarchar(50) NULL, " +
20.                         "Weight nvarchar(50) NULL")]
21.     public static IEnumerable BarCodeParsercs(SqlString barcode)
22.     {
23.         return new String[]{barcode.Value};
24.     }
25.  
26.     private static IEnumerable GetData(SqlString barcode)
27.     {
29.         string BarCodeType = "";
30.         string PO = "";
31.         string PartNumber = "";
32.         string Qty = "";
33.         string DateCode = "";
34.         string SupplierName = "";
35.         string SupplierSite = "";
36.         string Date = "";
37.         string Weight = "";
38.  
39.         string Barcode = barcode.Value;
40.  
41.         BarCodeType = Barcode.Substring(0, 3);
42.         switch (BarCodeType)
43.         {
44.             case "]d2":
45.                 {
46.                     BarCodeType = "]d2";
47.                     Barcode = Barcode.Substring(3);
48.                     break;
49.                 }
50.             case "]C0":
51.                 {
52.                     BarCodeType = "]C0";
53.                     Barcode = Barcode.Substring(3);
54.                     break;
55.                 }
56.         }
57.  
58.         string[] sections = Barcode.Split('~');
59.  
60.         foreach (string section in sections)
61.         {
62.             string ThreeDigitCode = "";
63.             string TwoDigitCode = "";
64.             bool FoundMatch = false;
65.  
66.             if (section.Length > 3)
67.             {
68.                 ThreeDigitCode = section.Substring(0, 3);
69.             }
70.             if (section.Length > 2)
71.             {
72.                 TwoDigitCode = section.Substring(0, 2);
73.             }
74.  
75.             switch (ThreeDigitCode)
76.             {
77.                 case "241":
78.                     {
79.                         PartNumber = section.Substring(3);
80.                         FoundMatch = true;
81.                         break;
82.                     }
83.                 case "400":
84.                     {
85.                         PO = section.Substring(3);
86.                         FoundMatch = true;
87.                         break;
88.                     }
89.             }
90.             if (FoundMatch.Equals(false))
91.             {
92.                 switch (TwoDigitCode)
93.                 {
94.                     case "96":
95.                         {
96.                             Qty = section.Substring(2);
97.                             break;
98.                         }
99.                     case "93":
100.                         {
101.                             DateCode = section.Substring(2);
102.                             break;
103.                         }
104.                     case "91":
105.                         {
106.                             SupplierName = section.Substring(2);
107.                             break;
108.                         }
109.                     case "92":
110.                         {
111.                             SupplierSite = section.Substring(2);
112.                             break;
113.                         }
114.                     case "94":
115.                         {
116.                             Date = section.Substring(2);
117.                             break;
118.                         }
119.                     case "95":
120.                         {
121.                             Weight = section.Substring(2);
122.                             break;
123.                         }
124.                 }
125.             }
126.         }
127.  
128.         return new string[] { barcode.Value, BarCodeType, PO, PartNumber, Qty, DateCode, SupplierName, SupplierSite, Date, Weight };
129.     }
130.  
131.  
132.     public static void FillRow(Object obj,
133.                             out SqlString BarcodeType,
134.                             out SqlString PO,
135.                             out SqlString PartNumber,
136.                             out SqlString Qty,
137.                             out SqlString DateCode,
138.                             out SqlString SupplierName,
139.                             out SqlString SupplierSite,
140.                             out SqlString Date,
141.                             out SqlString Weight)
142.     {
143.         String barcode = (String)obj;
144.         String[] BarCode = (String[])GetData(barcode);
145.  
146.  
147.         BarcodeType = (SqlString)BarCode[1];
148.         PO = (SqlString)BarCode[2];
149.         PartNumber = (SqlString)BarCode[3];
150.         Qty = (SqlString)BarCode[4];
151.         DateCode = (SqlString)BarCode[5];
152.         SupplierName = (SqlString)BarCode[6];
153.         SupplierSite = (SqlString)BarCode[7];
154.         Date = (SqlString)BarCode[8];
155.         Weight = (SqlString)BarCode[9];
156.     }
157. };

Once the code has been succesfully deployed. I just need to call the function, in SSMS, like so:
SELECT* FROM [dbo].[BarCodeParsercs] (N']d2400PO_09928-1~2413000510_E~96400~9320100614-320008210~910278~9232~94')




Helpful Resources:

http://www.mssqltips.com/sqlservertip/2582/introduction-to-sql-server-clr-table-valued-functions/
http://www.mssqltips.com/sqlservertip/1986/sqlclr-function-to-return-free-space-for-all-drives-on-a-server/

Multi-Processor - Parallel SQL does not work well with SCOPE_IDENTITY()

Just something to be aware of, that was brought up at work. When working with parallel processing, the SCOPE_IDENTITY() might bring back the wrong id. This is true for SQL Server 2008 R2 and lower. This is suppose to be fix for SQL Server 2012.

http://connect.microsoft.com/SQLServer/feedback/details/328811/scope-identity-sometimes-returns-incorrect-value

http://blog.sqlauthority.com/2009/03/24/sql-server-2008-scope_identity-bug-with-multi-processor-parallel-plan-and-solution/

Mean while, use the OUTPUT to get the newly inserted value, if need be.

OLD*******************************

 INSERT INTO [Database1].[dbo].[Table1]

([Field1]
,[LDTS]
,[RS])
VALUES(@Field1, @LDTS, @RS)

SET @FieldID = SCOPE_IDENTITY()

NEW******************************

Declare @GetID Table (FieldID smallint)

INSERT INTO [Database1].[dbo].[Table1]

([Field1]
,[LDTS]
,[RS])
OUTPUT Inserted.FieldID into @GetID      
VALUES(@Field1, @LDTS, @RS)

Select Top 1  @FieldID = FieldID from @GetID

SQL CLR Aggregate: Median

Since Sql Server doesn't have an aggregate for Median, I figured, that I'll create my first SQL CLR to handle this problem.

So, I created my intial project, using the .Net Framework 3.5 and C# SQL CLR aggregate template, and started coding:

Code Snippet

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections.Generic;


[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(
    Format.UserDefined,
    IsInvariantToDuplicates = false,
    IsInvariantToNulls = false,
    IsInvariantToOrder = false,
    MaxByteSize = 8000)]
public struct Median : IBinarySerialize
{
    //Variables to hold the values;
    private List<double> ld;

    public void Init()
    {
        ld = new List<double>();
    }

    public void Accumulate(SqlDouble Value)
    {
        if (!Value.IsNull)
        {
            ld.Add(Value.Value);
        }
    }

    ///

    /// Merge the partially computed aggregate with this aggregate.
    ///
    /// The other partial results to be merged
    public void Merge(Median Group)
    {
        this.ld.AddRange(Group.ld.ToArray());
    }

    ///

    /// Called at the end of aggregation, to return the results.
    ///
    /// The median of all inputted values
    public SqlDouble Terminate()
    {
        //debug: return (SqlDouble)ld.Count;

        //special case 0 values
        if (ld.Count == 0)
            return SqlDouble.Null;

        ld.Sort();
        int index = (int)ld.Count / 2;

        if (ld.Count % 2 == 0)
        {
            return (SqlDouble)(((double)ld[index] + (double)ld[index - 1]) / 2);
        }
        else
        {
            return (SqlDouble)((double)ld[index]);
        }
    }


    #region IBinarySerialize Members

    public void Read(System.IO.BinaryReader r)
    {
        int cnt = r.ReadInt32();
        this.ld = new List<double>(cnt);
        for(int i = 0; i < cnt; i++)
        {
            this.ld.Add(r.ReadDouble());
        }
    }

    public void Write(System.IO.BinaryWriter w)
    {
        w.Write(this.ld.Count);
        foreach (double d in this.ld)
        {
            w.Write(d);
        }
    }

    #endregion
}

Some of the intial changes that I've made from the default:
1) The SqlUserDefinedAggregate default was Format.Native. The problem with using the default Serializer is that I needed a way to store my values, preferably in a List. By changing the format to Format.UserDefined  -- I was able to create my own serializer. In this case I used the IBinarySerialize interface.

2) I also changed the default IsInvariant attributes.

• IsInvariantToDuplicates - set to false since I want duplicates.
• IsInvariantToNull - set to false since I will handle Null values in my code.
• IsInvariantToOrder - set to false, I can do this in code.

Once the code is compiled, you will need to place the dll on the same machine as the SQL Server is located.

Using the following query to set the assembly and aggregate name:

Code Snippet

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'Median')
   DROP AGGREGATE Median
go

IF EXISTS (SELECT name FROM sys.assemblies WHERE name = 'MyClrCode')
   DROP ASSEMBLY MyClrCode
go


CREATE ASSEMBLY MyClrCode
FROM 'C:\Median.dll'
WITH PERMISSION_SET = SAFE
GO

CREATE AGGREGATE dbo.Median
(@input float)
RETURNS float
EXTERNAL NAME MyClrCode.Median

Once done, time to do some testing. An example:

Code Snippet

--Test 1 value
select
dbo.Median(x.y)
from
(
    select 1 as y
)x
GO

--Test Accuracy
select
dbo.Median(x.y)
from
(
    select 1 as y
    union all
    select 2 as y
)x
GO

SSRS: Saving User Settings from the Report

Sometimes, different report's parameters default values are needed for each users. Here is a way, each user can save their default settings for the next time they run an SSRS report, from the report itself.

We will need to save the user's settings to a database table.
Table create query:

SETQUOTED_IDENTIFIER ON
GO

SETANSI_PADDING ON
GO

CREATETABLE [dbo].[UserSettings](
[UserSettingSQN] [int] IDENTITY(1,1) NOT NULL,
[UserID] [varchar] (50) NOT NULL,
[ReportName] [varchar] (50) NOT NULL,
[ParameterName] [varchar](50) NULL,[ParameterValue] [varchar](max) NULL,
[LastUpdateDate] [datetime] NOT NULL,
CONSTRAINT [PK_UserSettingSQN] PRIMARY KEY CLUSTERED
(   [UserSettingSQN] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [IX_UserSettings] UNIQUE NONCLUSTERED
([UserID] ASC,
[ReportName] ASC,
[ParameterName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)ON [PRIMARY]
GO
SETANSI_PADDING OFF
GO

The fields:

• UserID: to store the user info that they used to access the report. I currently store both the computer and username.
• ReportName: The name of the report
• ParameterName: Parameter that is used in the report that is to be saved.
• ParameterValue: Value the user set to save.

An example of the data:

 The Report:

In the Reports, I've created 3 additional parameters, to be used for saving the user's default parameter settings:

• UserID: I use the SSRS's built-in field, User!UserID, to get the ID of the user running the report. Set the visibility to be internal.
• ReportName: Again, I use the SSRS's built-in field, Globals!ReportName, to get the name of the report as it is stored on the report server database. Set the visibility to be internal.
• Save: A boolean field that is visible to the user. I have the default set to "False", so when the user wants to change his/her settings they can change it to true when they run the report.

So when they run the report, I have the stored procedure check to see if the boolean was set to 1 -- if so then save with update/insert.

IF@Save = 1
BEGIN
UPDATE [ScientificArray].dbo.UserSettings WITH (SERIALIZABLE) SET ParameterValue = @StringNames
WHERE UserID = @UserID AND ReportName = @ReportName AND ParameterName = 'InverterString'
IF @@ROWCOUNT = 0
BEGIN
INSERT INTO [ScientificArray].dbo.UserSettings (UserID, ReportName, ParameterName, ParameterValue, LastUpdateDate) VALUES (@UserID, @ReportName, 'InverterString', @StringNames, GETDATE())
END
END

Removing accents for string comparisons in tsql

I've been reading The Daily WTF, and found this one interesting: http://thedailywtf.com/Articles/The-AntiSQL-Coalition-.aspx

By using the Latin1_General_CI_AI   (AI stands for Accent Insensitive)
the user was able to by-pass special characters like: É,È,Ê, and Ë for E when doing a search.
Example:

SELECT*
FROMPersons
WHEREname collate Latin1_General_CI_AI LIKE @name
AND surname collate Latin1_General_CI_AI LIKE @surname
ORDERBY name, surname

Additional references:
http://msdn.microsoft.com/en-us/library/ms187582.aspx


P.S. Funny to see other companies have Database haters too.

TSQL Example: Cumulative

A quick example on implementing cumaltive logic in a tsql query:

WITH CTE(Name, Observation, RowNum)
AS
(
SELECT
Name,
count(*) as Observation,
ROW_NUMBER() OVER (ORDER BY count(*)desc) as rownum
FROM
dbo.TABLE_LOGIC_STUFF
GROUP BY Name
)

select
c1.Name,
c1.Observation,
(select sum(c2.Observation) from cte as c2 where c2.RowNum <= c1.RowNum) as cumulative
from cte as c1



by adding row number in the cte table, I can then use a subquery to sum all the values below the current rownum.