Archiv für den Monat: Juni 2012

Unused Indexes

very easy to find unused indexes on your sql box, this code comes from Richard Douglas

 

/*
Script to check for indexes that have a greater write to read ratio
and have been updated in the last week.

Original script by Glenn Berry
Modified by Richard Douglas
	Removed Primary Keys from resultset
	Removed unique indices from resultset
	Added a date range for the query.
	Added a query to show server uptime to add quantification to the figures.
	Added [SpaceUsed KB] attribute
*/
SELECT
	Create_Date [UpSince],
	DATEDIFF(DD,Create_Date, GETDATE()) [UpTimeInDays]
FROM sys.databases WHERE name = 'tempdb';
go

DECLARE
	@Today DATETIME,
	@LastWeek DATETIME; 

SELECT
	@Today = GETDATE(),
	@LastWeek = DATEADD(DD,-7,@Today);

SELECT
	DB_Name() [DBName],
	OBJECT_NAME(s.[object_id]) AS [Table Name],
	i.name AS [Index Name],
	i.index_id,
    user_updates AS [Total Writes],
    user_seeks + user_scans + user_lookups AS [Total Reads],
    user_updates - (user_seeks + user_scans + user_lookups) AS [Difference],
	ISNULL((select 8192 * SUM(a.used_pages - CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END)
		FROM sys.partitions as p
		JOIN sys.allocation_units as a ON a.container_id = p.partition_id
		WHERE p.object_id = i.object_id
		AND p.index_id = i.index_id)/1024
	,0.0) AS [SpaceUsed KB]
FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON s.[object_id] = i.[object_id]
AND i.index_id = s.index_id
WHERE OBJECTPROPERTY(s.[object_id],'IsUserTable') = 1
AND s.database_id = DB_ID()
AND user_updates > (user_seeks + user_scans + user_lookups)
AND i.index_id > 1
AND user_seeks + user_scans + user_lookups = 0
AND Last_User_Update BETWEEN @LastWeek AND @Today
AND Is_Primary_Key = 0
AND IS_Unique = 0
ORDER BY [Difference] DESC, [Total Writes] DESC, [Total Reads] ASC;

Find Most Expensive Queries Using DMV

SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.total_logical_writes, qs.last_logical_writes,
qs.total_worker_time,
qs.last_worker_time,
qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_logical_reads DESC -- logical reads
-- ORDER BY qs.total_logical_writes DESC -- logical writes
-- ORDER BY qs.total_worker_time DESC -- CPU time

SQL–Partition SPLIT takes forever to create new empty partition

SQL–Partition SPLIT takes forever to create new empty partition

So you have a huge partition at the end because you forgot to create new empty partitions. You could do this (will be an offline operation):

  • drop all unaligned indexes
  • create an empty staging table, with identical structure as your data table (including all aligned indexes) with the proper constraints to accept the last partition
  • switch the last partition into the staging table. now the data has moved into the staging table, the last partition of the real table is empty
  • split the partitioning function as needed, since the last partition is empty the operations will be instantaneous. Remember to leave an empty last partition
  • create a temporary partitioning scheme for the staging table aligned with the partitions in the tail of the real table
  • rebuild the staging table on the temporary partition scheme created above. Now the staging table is aligned with the empty partitions in the tail of the real table. If the rebuild has log space issues use an online rebuild and take frequent log backups. consider also enabling bulk_logged mode.
  • switch each partition one by one from the staging table into the real table.
  • drop the staging table and temp partitioning function/scheme
  • start maintaining the partitioning so you don’t have to repeat this next time

Very useful article about the problem

My problem solver :D

Orignal post

 

self-signed SLL certificate

During development, we may want to create certificates for our own purposes and then implicitly trust them. We don’t really want to go to a certificate authority and get a signed certificate.

The traditional way to create a test certificate is to use the MakeCert tool that comes with the .NET Framework. You can find instructions about how to use this tool in the MSDN® Library, Certificate Creation Tool.

However, an easier tool to use is the SelfSSL tool, which is included in the IIS 6.0 Resource Kit Tools. SelfSSL creates a self-signed SSL server certificate that is automatically installed into IIS.

After opening a command prompt window, if you need help about using this tool, you can type the following command.

selfssl /?

To install a self-signed SLL certificate  on your desktop computer, run the following command.

selfssl /T

When the tool prompts you to replace the settings for site 1 (usually the Default Site), if you answer yes, the tool installs the certificate. The /T parameter ensures that Microsoft Internet Explorer® trusts the certificate (that is, you will see a confirmation message appear when you browse to a secure virtual directory).

 

sys.dm_exec_query_plan

get cached query plans from your SQL Server

 nice script to see the cached query plans for your SQL Server Instance

SELECT TOP 10
p.*,
q.*,
qs.*,
cp.plan_handle
FROM
sys.dm_exec_cached_plans cp
CROSS apply sys.dm_exec_query_plan(cp.plan_handle) p
CROSS apply sys.dm_exec_sql_text(cp.plan_handle) AS q
JOIN sys.dm_exec_query_stats qs
ON qs.plan_handle = cp.plan_handle
WHERE
cp.cacheobjtype = 'Compiled Plan' AND
p.query_plan.value('declare namespace
p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
max(//p:RelOp/@Parallel)', 'float') > 0
OPTION (MAXDOP 1)

 

MDX/SSRS: How to remove the ALL member from your parameter

if you create a report with a drop down parameter that allows multiple selections
and you used the query designer to create the MDX for you.

your MDX will look like:

WITH 
MEMBER [Measures].[ParameterCaption] 
AS 
[System].[By Systemtype].CURRENTMEMBER.MEMBER_CAPTION 

MEMBER [Measures].[ParameterValue] 
AS [System].[By Systemtype].CURRENTMEMBER.UNIQUENAME 

MEMBER [Measures].[ParameterLevel] 
AS
[System].[By Systemtype].CURRENTMEMBER.LEVEL.ORDINAL 

SELECT 
{
[Measures].[ParameterCaption], 
[Measures].[ParameterValue], 
[Measures].[ParameterLevel]
} 
ON COLUMNS , [System].[By Systemtype].ALLMEMBERS ON ROWS FROM [CounterData]

some users don’T want to see ‘Select All’ in the drop down, you should use the Children Function to fix this.

WITH 
MEMBER [Measures].[ParameterCaption] 
AS 
[System].[By Systemtype].CURRENTMEMBER.MEMBER_CAPTION 

MEMBER [Measures].[ParameterValue] 
AS [System].[By Systemtype].CURRENTMEMBER.UNIQUENAME 

MEMBER [Measures].[ParameterLevel] 
AS
 [System].[By Systemtype].CURRENTMEMBER.LEVEL.ORDINAL 
 
 SELECT 
 {
 [Measures].[ParameterCaption], 
 [Measures].[ParameterValue], 
 [Measures].[ParameterLevel]
} 
ON COLUMNS , [System].[By Systemtype].CHILDREN ON ROWS FROM [CounterData]