Pivoting SQL Data for Instant Reporting

I always get a bit of geeky enjoyment out of finding an excuse to take advantage of features introduced into newer versions of SQL server. Today, a coworker asked me to help him pivot some data from an issue tracking table he was using. He didn’t know what “pivot” was, but that is what he wanted.
Here’s the table with sample data:
CREATE TABLE [dbo].[Issues](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [WorkItem] [nvarchar](50) NOT NULL,
    [DateSubmitted] [date] NOT NULL,
    [DateFixed] [date] NULL,
PRIMARY KEY CLUSTERED (
    [Id] ASC
)
)
INSERT [dbo].[Issues](
    [WorkItem],
    [DateSubmitted],
    [DateFixed]
)
VALUES
('Test', '20130310', '20130310'),
('Test1', '20130310', NULL),
('Test2', '20130311', '20130313'),
('Test3', '20130311', '20130311'),
('Test4', '20130311', '20130320'),
('Test5', '20130312', '20130314'),
('Test6', '20130314', '20130315'),
('Test7', '20130314', '20130315'),
('Test8', '20130316', '20130319'),
('Test9', '20130319', '20130330'),
('Test10', '20130320', NULL)
He asked me to go through the table, and for each day count how many issues were submitted and how many were fixed. When he manually counted the values, he got the following results:

Date	Submitted	Fixed
3/10/2013	2	1
3/11/2013	3	1
3/12/2013	1	0
3/13/2013	0	1
3/14/2013	2	1
3/15/2013	0	2
3/16/2013	1	0
3/19/2013	1	1
3/20/2013	1	1
3/30/2013	0	1

After staring at the tables for a few moments, I realized that the easiest way to transform the table into the results was to use PIVOT.
Select [Date]
, ISNULL([Submitted],0) as [Submitted]
, ISNULL([Fixed],0) as [Fixed]
FROM
  (Select DateFixed as [Date], COUNT(Id) as ct, 'Fixed' as md
     from dbo.[Issues]
    where DateFixed is not NULL
group by DateFixed
UNION ALL
   Select DateSubmitted as [Date], COUNT(Id) as ct, 'Submitted' as md
     from dbo.[Issues]
    where DateSubmitted is not NULL
group by DateSubmitted) as SourceTable
PIVOT
  (sum(ct)
   for md in ([Submitted], [Fixed])) as pvt
order by [Date]
And there you go. Fun day with new PIVOT-y toys.

Easy Trace File Query

Whenever a customer has an issue with slowness, we request a trace file to help us figure out where the problem may be. The trace allows us to see which procs are called most often, and which are taking up the most time.

I used to import the trace file into a temporary table, then run my own queries on the file to pinpoint issues. While that works well, it requires me to make additional copies of the customer's data which all has to be tracked due to in-house security rules. Since I don't like having to do excessive paperwork, I found a way to easily read in the data from where it sits rather than importing it into SQL server:

Select ObjectName
, substring(DatabaseName, 1, 18) as dbname
, AVG(cpu) as cpuuse
, AVG(reads) as avgreads
, AVG(writes) as avgwrites
, COUNT(EndTime) as instances
, AVG(duration) as avgduration
, COUNT(EndTime) * AVG(reads) as cumexpense
from fn_trace_gettable ('C:\Temp\MyTraceFile.trc', DEFAULT)
group by ObjectName, substring(DatabaseName, 1, 18)
order by avgreads desc

The above query orders the items in the trace by average number of reads. Next, order the list by Average CPU then Average Duration. Any queries that are consistently at the top of the list are the first candidates for tuning.

I've also used the following to zero in on data regarding specific procs:

select  DatabaseName, ObjectName, TextData, CPU, Reads, Writes
from fn_trace_gettable ('C:\Temp\MyTraceFile.trc', DEFAULT)
where ObjectName ='MyStoredProc' or ObjectName='YourStoredProc'

Have fun analyzing your trace files.