Sql Server Tuning for Dummies

Thursday, March 17, 2016

Who has the DAC

https://www.brentozar.com/archive/2011/08/dedicated-admin-connection-why-want-when-need-how-tell-whos-using/

SELECT
CASE
WHEN ses.session_id= @@SPID THEN 'It''s me! '
ELSE '' END
+ coalesce(ses.login_name,'???') as WhosGotTheDAC,
ses.session_id,
ses.login_time,
ses.status,
ses.original_login_name
from sys.endpoints as en
join sys.dm_exec_sessions ses on
en.endpoint_id=ses.endpoint_id
where en.name='Dedicated Admin Connection'

Friday, January 15, 2016

Check Space for mounted drives

USE [msdb]
GO

/****** Object: Job [DBA_CheckDriveSpace] Script Date: 1/15/2016 8:46:13 AM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [Database Maintenance] Script Date: 1/15/2016 8:46:13 AM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'DBA_CheckDriveSpace',
@enabled=0,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'Database Maintenance',
@owner_login_name=N'sa',
@notify_email_operator_name=N'Team_DBA', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [CheckDriveSpace] Script Date: 1/15/2016 8:46:14 AM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'CheckDriveSpace',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'PowerShell',
@command=N'###############################################################################
# Check the percentage of free space, including mount points
###############################################################################
# For use in sending alerts via SQL Server Database Mail and may be scheduled
# via a SQL Server Agent job. Create a new Agent job and creat a new step, set
# the Type as PowerShell, edit the code below to put in your server name(s),
# Database Mail profile name, and recipient email address(es), optionally
# change the warning threshold, then paste the code into the Command pane and
# create a schedule.
###############################################################################

# List the servers you want to check for low disk capacities
$ServerArray = @(''AWOTORPRODSQL01'', ''AWOTORPRODSQL02'')
# Server names (not SQL Server instance names) must be in single quotes and
# separated by commas. If the SQL Server Agent service account does not have
# admin permissions on remote servers, you must assign Remote Execute
# permissions to the Agent account on each remote server, and the Agent account
# must be a domain account. To assign Remote Execute, run wmimgmt.msc,
# right-click/Properties, select the Security tab, expand the Root node, select
# the CIMV2 node, click the Security button, add the Agent account and scroll
# down to find and check the box for the "Remote Enable" permission.

import-module "sqlps" -DisableNameChecking

# Set threshold percentage for low disk capacity warnings.
$Threshold = .2
# Threshold value must be between 0 and 1.
# (E.g. ".1" will produce warnings when free space is below 10%.

# Set values for units of measure
[string]$UnitOfMeasure = ''1GB''
$UnitOfMeasureTerm = "GB"
# Use an empty string for bytes or KB/MB/GB/TB/PB
# (KB-PB are constants in PowerShell)

ForEach ($ServerName in $ServerArray)
{
$Volumes = Get-WmiObject -namespace "root/cimv2" -computername $ServerName -query "SELECT Name, Capacity, FreeSpace FROM Win32_Volume WHERE DriveType = 2 OR DriveType = 3"

ForEach ($Volume in $Volumes)
{
[string]$DriveType = Switch($Volume.DriveType)
{
0{''Unknown''}
1{''No Root Directory''}
2{''Removable Disk''}
3{''Local Disk''}
4{''Network Drive''}
5{''Compact Disk''}
6{''RAM Disk''}
default {''Unknown''}
}
[string]$Drive = "Drive: {0}" -f$Volume.Name
[string]$Capacity = "Capacity: {0} {1}" -f[System.Math]::Round(($Volume.Capacity / $UnitOfMeasure),0), $UnitOfMeasureTerm
[string]$FreeSpace = "Free Space: {0} {1}" -f[System.Math]::Round(($Volume.FreeSpace / $UnitOfMeasure),0), $UnitOfMeasureTerm
[string]$PercentFree = "Percent Free Space: " + [System.Math]::Round(($Volume.FreeSpace / $Volume.Capacity), 2)*100 + "%"

# Send an email alert via Database Mail if a disk is below the warning threshold
If ($Volume.FreeSpace / $Volume.Capacity -lt $Threshold)
{
$Qry = "EXECUTE msdb.dbo.sp_send_dbmail @profile_name = ''DB Errors'',
@recipients = ''Team_DBA2@activenetwork.com'',
@subject = ''AWOTORPRODSQL Low Disk Capacity Warning'',
@body_format = ''text'',
@body = ''WARNING: Low free space on " + $ServerName.ToUpper() + ".
" + $Drive + "
" + $Capacity + "
" + $FreeSpace + "
" + $PercentFree + "''"
# Replace with the name of the Database Mail profile name.
# Replace with who you want the email to go to.
# Changing the formatting of $Qry will change the format of the email.

Invoke-SqlCmd -query $Qry -ServerInstance AWOTORPRODSQL
# If the local instance of SQL Server is a named instance, use the
# "DOMAIN\InstanceName" in place of ''localhost''.
}

###########################################################################
# If you''d like to get a disk capacity report interactively, comment-out
# the If statement above and comment-in the following If statement:

If ($Volume.FreeSpace / $Volume.Capacity -lt .1)
{
Write-Output "WARNING: Disk capacity is less than 10%"
Write-Output $Drive
Write-Output $Capacity
Write-Output $FreeSpace
Write-Output $PercentFree
}
Else
{
Write-Output $Drive
Write-Output $Capacity
Write-Output $FreeSpace
Write-Output $PercentFree
}
}
}
',
@database_name=N'master',
@output_file_name=N'V:\Volumes\Backups01\Scripts\CheckSpace.txt',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Every half hour',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=4,
@freq_subday_interval=30,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20150115,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959,
@schedule_uid=N'dea34bfb-8396-4f14-ba12-0a0b282389fd'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

Monday, September 21, 2015

Reverse Engineers Database Mail Settings.

http://www.sqlservercentral.com/Forums/Topic982618-391-1.aspx

USE msdb
GO

Declare @TheResults varchar(max),
        @vbCrLf CHAR(2)
SET @vbCrLf = CHAR(13) + CHAR(10)        
SET @TheResults = '
use master
go
sp_configure ''show advanced options'',1
go
reconfigure with override
go
sp_configure ''Database Mail XPs'',1
--go
--sp_configure ''SQL Mail XPs'',0
go
reconfigure
go
'
SELECT @TheResults = @TheResults  + '
--#################################################################################################
-- BEGIN Mail Settings ' + p.name + '
--#################################################################################################
IF NOT EXISTS(SELECT * FROM msdb.dbo.sysmail_profile WHERE  name = ''' + p.name + ''') 
  BEGIN
    --CREATE Profile [' + p.name + ']
    EXECUTE msdb.dbo.sysmail_add_profile_sp
      @profile_name = ''' + p.name + ''',
      @description  = ''' + ISNULL(p.description,'') + ''';
  END --IF EXISTS profile
  '
  +
  '
  IF NOT EXISTS(SELECT * FROM msdb.dbo.sysmail_account WHERE  name = ''' + a.name + ''')
  BEGIN
    --CREATE Account [' + a.name + ']
    EXECUTE msdb.dbo.sysmail_add_account_sp
    @account_name            = ' + CASE WHEN a.name                IS NULL THEN ' NULL ' ELSE + '''' + a.name                  + '''' END + ',
    @email_address           = ' + CASE WHEN a.email_address       IS NULL THEN ' NULL ' ELSE + '''' + a.email_address         + '''' END + ',
    @display_name            = ' + CASE WHEN a.display_name        IS NULL THEN ' NULL ' ELSE + '''' + a.display_name          + '''' END + ',
    @replyto_address         = ' + CASE WHEN a.replyto_address     IS NULL THEN ' NULL ' ELSE + '''' + a.replyto_address       + '''' END + ',
    @description             = ' + CASE WHEN a.description         IS NULL THEN ' NULL ' ELSE + '''' + a.description           + '''' END + ',
    @mailserver_name         = ' + CASE WHEN s.servername          IS NULL THEN ' NULL ' ELSE + '''' + s.servername            + '''' END + ',
    @mailserver_type         = ' + CASE WHEN s.servertype          IS NULL THEN ' NULL ' ELSE + '''' + s.servertype            + '''' END + ',
    @port                    = ' + CASE WHEN s.port                IS NULL THEN ' NULL ' ELSE + '''' + CONVERT(VARCHAR,s.port) + '''' END + ',
    @username                = ' + CASE WHEN c.credential_identity IS NULL THEN ' NULL ' ELSE + '''' + c.credential_identity   + '''' END + ',
    @password                = ' + CASE WHEN c.credential_identity IS NULL THEN ' NULL ' ELSE + '''NotTheRealPassword''' END + ', 
    @use_default_credentials = ' + CASE WHEN s.use_default_credentials = 1 THEN ' 1 ' ELSE ' 0 ' END + ',
    @enable_ssl              = ' + CASE WHEN s.enable_ssl = 1              THEN ' 1 ' ELSE ' 0 ' END + ';
  END --IF EXISTS  account
  '
  + '
IF NOT EXISTS(SELECT *
              FROM msdb.dbo.sysmail_profileaccount pa
                INNER JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
                INNER JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id  
              WHERE p.name = ''' + p.name + '''
                AND a.name = ''' + a.name + ''') 
  BEGIN
    -- Associate Account [' + a.name + '] to Profile [' + p.name + ']
    EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
      @profile_name = ''' + p.name + ''',
      @account_name = ''' + a.name + ''',
      @sequence_number = ' + CONVERT(VARCHAR,pa.sequence_number) + ' ;
  END --IF EXISTS associate accounts to profiles
--#################################################################################################
-- Drop Settings For ' + p.name + '
--#################################################################################################
/*
IF EXISTS(SELECT *
            FROM msdb.dbo.sysmail_profileaccount pa
              INNER JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
              INNER JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id  
            WHERE p.name = ''' + p.name + '''
              AND a.name = ''' + a.name + ''')
  BEGIN
    EXECUTE msdb.dbo.sysmail_delete_profileaccount_sp @profile_name = ''' + p.name + ''',@account_name = ''' + a.name + '''
  END 
IF EXISTS(SELECT * FROM msdb.dbo.sysmail_account WHERE  name = ''' + a.name + ''')
  BEGIN
    EXECUTE msdb.dbo.sysmail_delete_account_sp @account_name = ''' + a.name + '''
  END
IF EXISTS(SELECT * FROM msdb.dbo.sysmail_profile WHERE  name = ''' + p.name + ''') 
  BEGIN
    EXECUTE msdb.dbo.sysmail_delete_profile_sp @profile_name = ''' + p.name + '''
  END
*/
  ' 
FROM msdb.dbo.sysmail_profile p
INNER JOIN msdb.dbo.sysmail_profileaccount pa ON  p.profile_id = pa.profile_id
INNER JOIN msdb.dbo.sysmail_account a         ON pa.account_id = a.account_id 
LEFT OUTER JOIN msdb.dbo.sysmail_server s     ON a.account_id = s.account_id
LEFT OUTER JOIN sys.credentials c    ON s.credential_id = c.credential_id

   ;WITH E01(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL  
                    SELECT 1 UNION ALL SELECT 1 UNION ALL  
                    SELECT 1 UNION ALL SELECT 1 UNION ALL  
                    SELECT 1 UNION ALL SELECT 1 UNION ALL  
                    SELECT 1 UNION ALL SELECT 1), --         10 or 10E01 rows  
         E02(N) AS (SELECT 1 FROM E01 a, E01 b),  --        100 or 10E02 rows  
         E04(N) AS (SELECT 1 FROM E02 a, E02 b),  --     10,000 or 10E04 rows  
         E08(N) AS (SELECT 1 FROM E04 a, E04 b),  --100,000,000 or 10E08 rows  
         --E16(N) AS (SELECT 1 FROM E08 a, E08 b),  --10E16 or more rows than you'll EVER need,  
         Tally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E08),  
       ItemSplit(  
                 ItemOrder,  
                 Item  
                ) as (  
                      SELECT N,  
                        SUBSTRING(@vbCrLf + @TheResults + @vbCrLf,N + DATALENGTH(@vbCrLf),CHARINDEX(@vbCrLf,@vbCrLf + @TheResults + @vbCrLf,N + DATALENGTH(@vbCrLf)) - N - DATALENGTH(@vbCrLf))  
                      FROM Tally  
                      WHERE N < DATALENGTH(@vbCrLf + @TheResults)  
                      --WHERE N < DATALENGTH(@vbCrLf + @INPUT) -- REMOVED added @vbCrLf  
                        AND SUBSTRING(@vbCrLf + @TheResults + @vbCrLf,N,DATALENGTH(@vbCrLf)) = @vbCrLf --Notice how we find the delimiter  
                     )  
  select  
    row_number() over (order by ItemOrder) as ItemID,  
    Item  
  from ItemSplit

Monday, August 17, 2015

Check for AutoGrow

http://blogs.msdn.com/b/blogdoezequiel/archive/2010/11/16/the-sql-swiss-army-knife-5-checking-autogrow-times.aspx#.VdIE1_lViko

DECLARE @curr_tracefilename VARCHAR(500), @indx int, @base_tracefilename VARCHAR(500);
SELECT @curr_tracefilename = path FROM sys.traces WHERE is_default = 1;
SET @curr_tracefilename = REVERSE(@curr_tracefilename);
SELECT @indx = PATINDEX('%\%', @curr_tracefilename) ;
SET @curr_tracefilename = REVERSE(@curr_tracefilename) ;
SET @base_tracefilename = LEFT( @curr_tracefilename,LEN(@curr_tracefilename) - @indx) + '\log.trc' ;
WITH AutoGrow_CTE (databaseid, filename, Growth, Duration, StartTime, EndTime)
AS
(
SELECT databaseid, filename, SUM(IntegerData*8) AS Growth, Duration, StartTime, EndTime--, CASE WHEN EventClass =
FROM ::fn_trace_gettable(@base_tracefilename, default)
WHERE EventClass >= 92 AND EventClass <= 95
AND DATEDIFF(hh,StartTime,GETDATE()) < 24 -- Last 24h
GROUP BY databaseid, filename, IntegerData, Duration, StartTime, EndTime
)
SELECT DB_NAME(database_id) AS DatabaseName,
mf.name AS LogicalName,
mf.size*8 AS CurrentSize_KB,
mf.type_desc AS 'File_Type',
CASE WHEN is_percent_growth = 1 THEN 'Percentage' ELSE 'Pages' END AS 'Growth_Type',
ag.Growth AS Growth_KB,
Duration/1000 AS Duration_ms,
ag.StartTime,
ag.EndTime
FROM sys.master_files mf
LEFT OUTER JOIN AutoGrow_CTE ag
ON mf.database_id=ag.databaseid
AND mf.name=ag.filename
WHERE ag.Growth > 0 --Only where growth occurred
GROUP BY database_id, mf.name, mf.size, ag.Growth, ag.Duration, ag.StartTime, ag.EndTime, is_percent_growth, mf.growth, mf.type_desc
ORDER BY DatabaseName, LogicalName, ag.StartTime

Friday, July 24, 2015

Sp_changearticle schema_option

declare @schema_option varbinary(8) = 0x0000000008000001 --< PUT YOUR SCHEMA_OPTION HERE

set nocount on

declare @OptionTable table ( HexValue varbinary(8), IntValue as cast(HexValue as bigint), OptionDescription varchar(255))

insert into @OptionTable (HexValue, OptionDescription)

select 0x01 ,'Generates object creation script'

union all select 0x02 ,'Generates procs that propogate changes for the article'

union all select 0x04 ,'Identity columns are scripted using the IDENTITY property'

union all select 0x08 ,'Replicate timestamp columns (if not set timestamps are replicated as binary)'

union all select 0x10 ,'Generates corresponding clustered index'

union all select 0x20 ,'Converts UDT to base data types'

union all select 0x40 ,'Create corresponding nonclustered indexes'

union all select 0x80 ,'Replicate pk constraints'

union all select 0x100 ,'Replicates user triggers'

union all select 0x200 ,'Replicates foreign key constraints'

union all select 0x400 ,'Replicates check constraints'

union all select 0x800 ,'Replicates defaults'

union all select 0x1000 ,'Replicates column-level collation'

union all select 0x2000 ,'Replicates extended properties'

union all select 0x4000 ,'Replicates UNIQUE constraints'

union all select 0x8000 ,'Not valid'

union all select 0x10000 ,'Replicates CHECK constraints as NOT FOR REPLICATION so are not enforced during sync'

union all select 0x20000 ,'Replicates FOREIGN KEY constraints as NOT FOR REPLICATION so are not enforced during sync'

union all select 0x40000 ,'Replicates filegroups'

union all select 0x80000 ,'Replicates partition scheme for partitioned table'

union all select 0x100000 ,'Replicates partition scheme for partitioned index'

union all select 0x200000 ,'Replicates table statistics'

union all select 0x400000 ,'Default bindings'

union all select 0x800000 ,'Rule bindings'

union all select 0x1000000 ,'Full text index'

union all select 0x2000000 ,'XML schema collections bound to xml columns not replicated'

union all select 0x4000000 ,'Replicates indexes on xml columns'

union all select 0x8000000 ,'Creates schemas not present on subscriber'

union all select 0x10000000 ,'Converts xml columns to ntext'

union all select 0x20000000 ,'Converts (max) data types to text/image'

union all select 0x40000000 ,'Replicates permissions'

union all select 0x80000000 ,'Drop dependencies to objects not part of publication'

union all select 0x100000000 ,'Replicate FILESTREAM attribute (2008 only)'

union all select 0x200000000 ,'Converts date & time data types to earlier versions'

union all select 0x400000000 ,'Replicates compression option for data & indexes'

union all select 0x800000000 ,'Store FILESTREAM data on its own filegroup at subscriber'

union all select 0x1000000000 ,'Converts CLR UDTs larger than 8000 bytes to varbinary(max)'

union all select 0x2000000000 ,'Converts hierarchyid to varbinary(max)'

union all select 0x4000000000 ,'Replicates filtered indexes'

union all select 0x8000000000 ,'Converts geography, geometry to varbinary(max)'

union all select 0x10000000000 ,'Replicates geography, geometry indexes'

union all select 0x20000000000 ,'Replicates SPARSE attribute '

select HexValue,OptionDescription as 'Schema Options Enabled'

From @OptionTable where (cast(@schema_option as bigint) & cast(HexValue as bigint)) <> 0

select cast(

cast(0x01 AS BIGINT) --DEFAULT Generates object creation script

| cast(0x02 AS BIGINT) --DEFAULT Generates procs that propogate changes for the article

| cast(0x04 AS BIGINT) --Identity columns are scripted using the IDENTITY property

| cast(0x08 AS BIGINT) --DEFAULT Replicate timestamp columns (if not set timestamps are replicated as binary)

| cast(0x10 AS BIGINT) --DEFAULT Generates corresponding clustered index

--| cast(0x20 AS BIGINT) --Converts UDT to base data types

--| cast(0x40 AS BIGINT) --Create corresponding nonclustered indexes

| cast(0x80 AS BIGINT) --DEFAULT Replicate pk constraints

--| cast(0x100 AS BIGINT) --Replicates user triggers

--| cast(0x200 AS BIGINT) --Replicates foreign key constraints

--| cast(0x400 AS BIGINT) --Replicates check constraints

--| cast(0x800 AS BIGINT) --Replicates defaults

| cast(0x1000 AS BIGINT) --DEFAULT Replicates column-level collation

--| cast(0x2000 AS BIGINT) --Replicates extended properties

| cast(0x4000 AS BIGINT) --DEFAULT Replicates UNIQUE constraints

--| cast(0x8000 AS BIGINT) --Not valid

| cast(0x10000 AS BIGINT) --DEFAULT Replicates CHECK constraints as NOT FOR REPLICATION so are not enforced during sync

| cast(0x20000 AS BIGINT) --DEFAULT Replicates FOREIGN KEY constraints as NOT FOR REPLICATION so are not enforced during sync

--| cast(0x40000 AS BIGINT) --Replicates filegroups (filegroups must already exist on subscriber)

--| cast(0x80000 AS BIGINT) --Replicates partition scheme for partitioned table

--| cast(0x100000 AS BIGINT) --Replicates partition scheme for partitioned index

--| cast(0x200000 AS BIGINT) --Replicates table statistics

--| cast(0x400000 AS BIGINT) --Default bindings

--| cast(0x800000 AS BIGINT) --Rule bindings

--| cast(0x1000000 AS BIGINT) --Full text index

--| cast(0x2000000 AS BIGINT) --XML schema collections bound to xml columns not replicated

--| cast(0x4000000 AS BIGINT) --Replicates indexes on xml columns

| cast(0x8000000 AS BIGINT) --DEFAULT Creates schemas not present on subscriber

--| cast(0x10000000 AS BIGINT) --Converts xml columns to ntext

--| cast(0x20000000 AS BIGINT) --Converts (max) data types to text/image

--| cast(0x40000000 AS BIGINT) --Replicates permissions

--| cast(0x80000000 AS BIGINT) --Drop dependencies to objects not part of publication

--| cast(0x100000000 AS BIGINT) --Replicate FILESTREAM attribute (2008 only)

--| cast(0x200000000 AS BIGINT) --Converts date & time data types to earlier versions

| cast(0x400000000 AS BIGINT) --Replicates compression option for data & indexes

--| cast(0x800000000 AS BIGINT) --Store FILESTREAM data on its own filegroup at subscriber

--| cast(0x1000000000 AS BIGINT) --Converts CLR UDTs larger than 8000 bytes to varbinary(max)

--| cast(0x2000000000 AS BIGINT) --Converts hierarchyid to varbinary(max)

--| cast(0x4000000000 AS BIGINT) --Replicates filtered indexes

--| cast(0x8000000000 AS BIGINT) --Converts geography, geometry to varbinary(max)

--| cast(0x10000000000 AS BIGINT) --Replicates geography, geometry indexes

--| cast(0x20000000000 AS BIGINT) --Replicates SPARSE attribute

AS BINARY(8)) as Schema_Option

Thursday, July 23, 2015

IndexUsageAnalysis

USE [SysAdmin]
GO

/****** Object: Table [dbo].[DATAUSAGEANALYSIS] Script Date: 1/28/2015 5:14:29 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[DATAUSAGEANALYSIS](
[db_name] [nvarchar](128) NULL,
[schema_name] [nvarchar](128) NULL,
[table_name] [nvarchar](128) NULL,
[index_name] [sysname] NULL,
[type] [varchar](4) NULL,
[is_unique] [bit] NULL,
[cnstr] [varchar](2) NULL,
[key_columns] [nvarchar](max) NULL,
[included_columns] [nvarchar](max) NULL,
[location] [sysname] NOT NULL,
[rows] [bigint] NULL,
[pages] [bigint] NULL,
[MB] [decimal](9, 2) NULL,
[user_seeks] [bigint] NULL,
[user_scans] [bigint] NULL,
[user_lookups] [bigint] NULL,
[user_updates] [bigint] NULL,
[Statistics_last_collected] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

EXEC master..sp_MSForeachdb '
USE [?]
IF ''?'' <> ''master'' AND ''?'' <> ''model'' AND ''?'' <> ''msdb'' AND ''?'' <> ''tempdb''
BEGIN
INSERT INTO SYSADMIN..DATAUSAGEANALYSIS
exec [?]..sp_indexinfo @missing_ix = 0
END
'

select * from SYSADMIN..DATAUSAGEANALYSIS

Wednesday, July 22, 2015

RML Read Trace Summary

use sqlnexus
go
SELECT ub.OrigText AS OrigQuery,
ub.NormText AS NormQuery
,bh1.HashID
, bh1.AvgDuration_MS
, bh1.AvgCPU_MS
, bh1.AvgReads
, bh1.AvgWrites
, bh1.Calls
, bh1.SumCPU
, bh1.SumDuration
, bh1.SumReads
, bh1.SumWrites

, 100. * SUM(calls) / SUM(SUM(calls)) OVER() AS PctofTotalCalls,
ROW_NUMBER() OVER(ORDER BY SUM(calls) DESC) AS TotalCalls_rank

, 100. * SUM(calls*AvgDuration_MS) / SUM(SUM(calls*AvgDuration_MS)) OVER() AS PctofTotalDur,
ROW_NUMBER() OVER(ORDER BY SUM(calls*AvgDuration_MS) DESC) AS TotalDuration_rank

, 100. * SUM(calls*avgreads) / SUM(SUM(calls*avgreads)) OVER() AS PctofTotalReads,
ROW_NUMBER() OVER(ORDER BY SUM(calls*avgreads) DESC) AS TotalReads_rank

, 100. * SUM(calls*AvgWrites) / SUM(SUM(calls*AvgWrites)) OVER() AS PctofTotalWrites,
ROW_NUMBER() OVER(ORDER BY SUM(calls*AvgWrites) DESC) AS TotalWrites_rank

, 100. * SUM(calls*AvgCPU_MS) / SUM(SUM(calls*AvgCPU_MS)) OVER() AS PctofTotalCpu,
ROW_NUMBER() OVER(ORDER BY SUM(calls*AvgCPU_MS) DESC) AS TotalCPU_rank

--into SqlNexus_Awe0227

FROM (
SELECT ub.HashID, AVG(b.Duration)/1000.00 AS AvgDuration_MS, AVG(b.Reads) AS AvgReads, AVG(b.Writes) AS AvgWrites, AVG(b.CPU) AS AvgCPU_MS, COUNT(1) AS Calls, Sum(b.Duration) AS SumDuration, sum(b.Reads) AS SumReads, Sum(b.Writes) AS SumWrites, Sum(b.CPU) AS SumCPU
FROM ReadTrace.tblUniqueBatches AS ub
INNER JOIN ReadTrace.tblBatches AS b
ON ub.HashID = b.HashID
--where DBID in (599)
GROUP BY ub.HashID
) AS bh1
INNER JOIN ReadTrace.tblUniqueBatches AS ub
ON bh1.HashID = ub.HashID
group by ub.OrigText
,ub.NormText
,bh1.HashID
, bh1.AvgDuration_MS
,bh1.AvgCPU_MS
, bh1.AvgReads
, bh1.AvgWrites
, bh1.Calls
, bh1.SumCPU
, bh1.SumDuration
, bh1.SumReads
, bh1.SumWrites

order by SumDuration desc