Pull what reports were ran and by whom.

A very basic query to look at the ReportServer database to pull what reports there are, who has ran it, and how many times.

Code Snippet

1. SELECT
2. c.NAME,
3. el.UserName,
4. el.LATEST_RUN_DATE,
5. el.NUM_TIMES_RAN,
6. c.[path]
7. FROM
8. DBO.[CATALOG] AS c
9. LEFT JOIN
10. (
11.     SELECT
12.     EL.REPORTID
13.     ,EL.UserName
14.     ,COUNT(EL.TIMESTART) NUM_TIMES_RAN
15.     ,MAX(EL.TIMESTART) AS LATEST_RUN_DATE
16.     FROM
17.     dbo.EXECUTIONLOG AS el
18.     GROUP BY
19.     el.REPORTID, EL.UserName
20. )el on el.ReportID = c.ItemID
21. WHERE
22. C.[TYPE] = 2
23. ORDER BY c.NAME, el.UserName

Problems of not having query hints allowed in Views

So, working with Tableau, you're not given the option to use stored procedures, and I don't like to put queries inline within the code, so I create views for the reports to pull data from. The problem I have is that the query I'm running, is doing a count, which ends up doing in parallel; giving erroneous totals. No problem, not the first time I ran into this problem, just use OPTION (MAXDOP 1).

The problem with that, is Views are not allowed to have query hints. So the next possible solution would be to call the option with the view:

SELECT * FROM [dbo].[View1]
OPTION (MAXDOP 1);

This would work, but since I'm using Tableau; it just errors out when I try to add the option.

So, is there another path? The next possible, semi-working option is to create a "plan guide" for the query. (More info found at: http://msdn.microsoft.com/en-us/library/ms179880.aspx)

Example:
EXEC sp_create_plan_guide
@name = N'Guide1',
@stmt = N'SELECT * FROM [dbo].[View]',
@type = N'SQL',
@module_or_batch = NULL,
@params = NULL,
@hints = N'OPTION (MAXDOP 1)';

This works, if the query executed is the exact same as in the statement. So, if there is an extra space, or different formatting on the query this guide would not be called. Luckily, I'm the only one reports developer, this should be fine -- as long as no one messes with the query and that I have ALTER permissions on the databases that this will be placed on.

There are problems with this solution, but first to see the plan guides, you can use this query:

SELECT * FROM sys.plan_guides

Since, only 1 plan guide is allowed to be enabled for a query, and worst of all -- the possible future errors:
"Trying to drop or modify a function, stored procedure, or DML trigger that is referenced by a plan guide, either enabled or disabled, causes an error. Trying to drop a table that has a trigger defined on it that is referenced by a plan guide also causes an error."

So, if you ended up using the Object type, instead of the Sql type you will surely need to know how to drop a plan:

EXEC sp_control_plan_guide N'DROP', N'Guide1'


I still have a feeling in my bones that there is an easier way, or at least there should be. It shouldn't have to be this ugly.





 

A couple of Linq to Sql Query Examples

Just a quick reference of a couple basic query conversions for Linq to SQL

Code Snippet

21. //inner join
22.             //SELECT [t0].[CountyName]
23.             //FROM [dbo].[County] AS [t0]
24.             //INNER JOIN [dbo].[State] AS [t1] ON [t0].[StateID] = [t1].[StateID]
25.             //WHERE LOWER([t1].[StateName]) = @p0
26.             var counties =  from c in db.Counties
27.                             join st in db.States on c.StateID equals st.StateID
28.                             where st.StateName.ToLower().Equals("florida")
29.                             select c.CountyName;
30.  
31.  
32.             //left join -- use DefaultIfEmpty and new object
33.             //SELECT [t0].[CountyName],
34.             //(CASE WHEN [t2].[test] IS NULL THEN CONVERT(NVarChar(50),@p0)
35.             //      ELSE CONVERT(NVarChar(50),[t2].[StateName])
36.             //      END) AS [StateName]
37.             //FROM [dbo].[County] AS [t0]
38.             //LEFT OUTER JOIN
39.             //(
40.             //      SELECT 1 AS [test], [t1].[StateID], [t1].[StateName]
41.             //      FROM [dbo].[State] AS [t1]
42.             //) AS [t2] ON [t0].[StateID] = [t2].[StateID]
43.             var counties2 = from c in db.Counties
44.                             join st in db.States on c.StateID equals st.StateID
45.                             into stateCounties
46.                             from co in stateCounties.DefaultIfEmpty()
47.                             select new { CountyName = c.CountyName, StateName = (co == null)? "": co.StateName };
48.  
49.  
50.             //Same table query
51.             //SELECT [t0].[CountyName]
52.             //FROM [dbo].[County] AS [t0],
53.             //     [dbo].[County] AS [t1]
54.             //WHERE ([t0].[CountyName] = [t1].[CountyName]) AND ([t0].[CountyID] <> [t1].[CountyID])
55.             var counties3 = from c1 in db.Counties
56.                             from c2 in db.Counties
57.                             where c1.CountyName == c2.CountyName && c1.CountyID != c2.CountyID
58.                             select new
59.                             {
60.                                 CountyName = c1.CountyName,
61.                             };
62.  
63.  
64.  
65.  
66.             var counties4 = (from c1 in db.Counties
67.                             from c2 in db.Counties
68.                             where c1.CountyName == c2.CountyName && c1.CountyID != c2.CountyID
69.                             group c1 by c1.CountyName into dups
70.                             select new
71.                             {
72.                                 CountyName = dups.Key,
73.                                 Count = dups.Count()
74.                             }
75.                             ).OrderBy(n => n.CountyName);

Some resources I found useful:
http://codesamplez.com/database/linq-to-sql-join-tutorials
http://weblogs.asp.net/rajbk/archive/2010/03/12/joins-in-linq-to-sql.aspx
http://msdn.microsoft.com/en-us/library/bb386913.aspx
   More specifically to joins:
   http://msdn.microsoft.com/en-us/library/bb399397.aspx

SQL-Server Query to get blocking information

A useful query to get the blocking information within SQL-Server:

SELECT
tr1.resource_type,
tr1.resource_subtype,
tr1.resource_database_id,
tr1.resource_associated_entity_id,
tr1.request_mode,
tr1.request_type,
tr1.request_status,
tr1.request_session_id,
tr1.request_owner_type,
tr2.blocking_session_id
FROM sys.dm_tran_locks as tr1
INNER JOIN sys.dm_os_waiting_tasks as tr2 ON tr1.lock_owner_address = tr2.resource_address;

-Source: Microsoft SQL Server 2012 - Pocket Consultant by William R. Stanek

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.

SQL Server Query Performance Analysis

Great post by Carl Nolan(http://blogs.msdn.com/b/mcsuksoldev/archive/2011/11/27/adventure-in-tsql-sql-server-query-performance-analysis-using-dmvs.aspx) on finding the worst offending queries that do the most CPU and Disk I/O loads.

The two queries are:

CPU Query

-- Which Queries are taking the most time/cpu to execute
SELECT TOP 20
    total_worker_time, total_elapsed_time,
    total_worker_time/execution_count AS avg_cpu_cost, execution_count,
    (SELECT DB_NAME(dbid) + ISNULL('..' + OBJECT_NAME(objectid), '')
        FROM sys.dm_exec_sql_text([sql_handle])) AS query_database,
    (SELECT SUBSTRING(est.[text], statement_start_offset/2 + 1,
        (CASE WHEN statement_end_offset = -1
            THEN LEN(CONVERT(nvarchar(max), est.[text])) * 2
            ELSE statement_end_offset
            END - statement_start_offset) / 2
        )
        FROM sys.dm_exec_sql_text([sql_handle]) AS est) AS query_text,
    total_logical_reads/execution_count AS avg_logical_reads,
    total_logical_writes/execution_count AS avg_logical_writes,
    last_worker_time, min_worker_time, max_worker_time,
    last_elapsed_time, min_elapsed_time, max_elapsed_time,
    plan_generation_num, qp.query_plan
FROM sys.dm_exec_query_stats
    OUTER APPLY sys.dm_exec_query_plan([plan_handle]) AS qp
WHERE [dbid] >= 5 AND DB_NAME(dbid) IS NOT NULL
  AND (total_worker_time/execution_count) > 100
--ORDER BY avg_cpu_cost DESC;
--ORDER BY execution_count DESC;
ORDER BY total_worker_time DESC;

 

Disk IO Query

SELECT TOP 20
    total_logical_reads/execution_count AS avg_logical_reads,
    total_logical_writes/execution_count AS avg_logical_writes,
    total_worker_time/execution_count AS avg_cpu_cost, execution_count,
    total_worker_time, total_logical_reads, total_logical_writes,
    (SELECT DB_NAME(dbid) + ISNULL('..' + OBJECT_NAME(objectid), '')
        FROM sys.dm_exec_sql_text([sql_handle])) AS query_database,
    (SELECT SUBSTRING(est.[text], statement_start_offset/2 + 1,
        (CASE WHEN statement_end_offset = -1
            THEN LEN(CONVERT(nvarchar(max), est.[text])) * 2
            ELSE statement_end_offset
            END - statement_start_offset
        ) / 2)
        FROM sys.dm_exec_sql_text(sql_handle) AS est) AS query_text,
    last_logical_reads, min_logical_reads, max_logical_reads,
    last_logical_writes, min_logical_writes, max_logical_writes,
    total_physical_reads, last_physical_reads, min_physical_reads, max_physical_reads,
    (total_logical_reads + (total_logical_writes * 5))/execution_count AS io_weighting,
    plan_generation_num, qp.query_plan
FROM sys.dm_exec_query_stats
    OUTER APPLY sys.dm_exec_query_plan([plan_handle]) AS qp
WHERE [dbid] >= 5 AND DB_NAME(dbid) IS NOT NULL
  and (total_worker_time/execution_count) > 100
ORDER BY io_weighting DESC;
--ORDER BY avg_logical_reads DESC;
--ORDER BY avg_logical_writes DESC;
--ORDER BY avg_cpu_cost DESC;