USE PerfDB; 
SET NOCOUNT ON; 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

/*
	Yaniv Etrogi
	http://www.sqlserverutilities.com	

*/

-- Verify your workload is evenly spread (ballanced) across all application servers
-- i.e. when there are several web servers running the same applications.
SELECT 
	 [Database]
  ,COUNT(*) AS Executions
	,[Application]
	,[Host]
FROM [dbo].[BaseLineFull]	
GROUP BY [Database], [Application], [Host]
ORDER BY [Database], [Application], Executions DESC, [Host];


-- Executions per Application
SELECT 
	[Application]
 ,COUNT(*) AS Executions
FROM [dbo].[BaseLineFull]	
GROUP BY [Application]
ORDER BY Executions DESC;


-- Executions per database
SELECT 
	[Database]
 ,COUNT(*) AS Executions
FROM [dbo].[BaseLineFull]	
GROUP BY [Database]
ORDER BY Executions DESC;


-- Database by Active time (in seconds).
SELECT 
	[Database]
 ,SUM(Duration) / 1000000 AS Total_Duration_ss
FROM [dbo].[BaseLineFull]	
GROUP BY [Database]
ORDER BY Total_Duration_ss DESC;


-- All clients that access the server.
SELECT DISTINCT Host FROM [dbo].[BaseLineFull] ORDER BY Host;


-- All Logins that access the server.
SELECT DISTINCT [Login] FROM [dbo].[BaseLineFull] ORDER BY [Login];


-- All Application that access the server.
SELECT DISTINCT [Application] FROM [dbo].[BaseLineFull] ORDER BY [Application];


-- Application by Active time (in seconds).
SELECT 
	[Application]
 ,SUM(Duration) / 1000000 AS Total_Duration_ss
FROM [dbo].[BaseLineFull]	
GROUP BY [Application]
ORDER BY Total_Duration_ss DESC;


-- Application by CPU usage (in seconds).
SELECT 
	[Application]
 ,SUM(CPU) /1000 AS Total_CPU_ss
FROM [dbo].[BaseLineFull]	
GROUP BY [Application]
ORDER BY Total_CPU_ss DESC;


-- Application by Reads.
SELECT 
	[Application]
 ,SUM(Reads) AS Total_Reads
FROM [dbo].[BaseLineFull]	
GROUP BY [Application]
ORDER BY Total_Reads DESC;


-- Application by Writes.
SELECT 
	[Application]
 ,SUM(Writes) AS Total_Writes
FROM [dbo].[BaseLineFull]	
GROUP BY [Application]
ORDER BY Total_Writes DESC;


-- Application by RowCounts.
-- A high number of RowCounts may indicate that a procedure is visting more data that may 
-- be required. 
-- This does not only have it's effect on performance but can affect concurrency as well.
SELECT 
	[Application]
 ,SUM(RowCounts) AS Total_RowCounts
FROM [dbo].[BaseLineFull]	
GROUP BY [Application]
ORDER BY Total_RowCounts DESC;


--sp_columns [BaseLineFull]