http://blogs.extremeexperts.com/2012/06/24/windows-powershell-links-to-bookmark/
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; How to Make SQL Queries Run Faster – Play
cool podcast by brent ozar…very useful if you plan to be professional dba,buy it if you want to know more about tuning.
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 SERVER – Find Currently Running Query
SELECT sqltext.TEXT, req.session_id, req.status, req.command, req.cpu_time, req.total_elapsed_time FROM sys.dm_exec_requests req CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
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
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).
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] 