March 27, 2009 at 7:11 am
The following script is used to collect data for the SQL Environment Report, and it has caused some locking and blocking issues on some of the SQL Servers. I need to look at this script and make suggestions for improvement. One very important requirement for this task is that WITH NOLOCK is added wherever possible throughout the entire script. Another important requirement is to pinpoint the location(s) of where locking and blocking is occurring and rewrite the corresponding SQL. This is a very import script and you need to be very thorough.
The Script:
USE master
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SQL_Server_Env]'))
drop table SQL_Server_Env
DECLARE @MaxDegParallelism int,
@MinSQLServerMemory int,
@MaxSQLServerMemory int,
@AWEEnabled int,
@SQLServerAgent varchar(50),
@MSDTC varchar(50),
@ProcessorCount varchar(50),
@ActiveNode char (50),
@OSRevision varchar (50) ,
@OSName varchar (50) ,
@Domain varchar(50),
@OSType varchar (50),
@CurrentVersion varchar(50),
@PhysicalMemory varchar(50),
@Env varchar(50),
@AppName varchar(50),
@dba-2 varchar(50),
@Node1 char (16),
@Node2 char (16)
SELECT @Env = Env, @AppName = AppName, @dba-2 = DBA FROM DBA
CREATE TABLE #xp_servicecontrol(
Value varchar(50))
CREATE TABLE #xp_msver(
index_value int NULL,
Name nvarchar(50) NULL,
Internal_Value int NULL,
Character_Value nvarchar(500) NULL)
CREATE TABLE #xp_regread(
index_value nvarchar(500) NULL,
index_value2 nvarchar(500) NULL,
Data nvarchar(500) NULL)
select @MaxDegParallelism = value from sysconfigures where config = 1539
select @MinSQLServerMemory = value from sysconfigures where config = 1543
select @MaxSQLServerMemory = value from sysconfigures where config = 1544
select @AWEEnabled = value from sysconfigures where config = 1548
INSERT INTO #xp_servicecontrol
EXECUTE master.dbo.xp_servicecontrol 'QUERYSTATE', 'SQLServerAgent'
SELECT @SQLServerAgent = Value FROM #xp_servicecontrol
DELETE FROM #xp_servicecontrol
INSERT INTO #xp_servicecontrol
EXECUTE master.dbo.xp_servicecontrol 'QUERYSTATE', 'MSDTC'
SELECT @MSDTC = Value FROM #xp_servicecontrol
DELETE FROM #xp_servicecontrol
INSERT INTO #xp_msver
EXECUTE xp_msver 'PhysicalMemory'
INSERT INTO #xp_msver
EXECUTE xp_msver 'ProcessorCount'
SELECT @ProcessorCount = convert(varchar(50),Internal_Value) from #xp_msver Where index_value = 16
SELECT @PhysicalMemory = convert(varchar(50),Internal_Value) from #xp_msver Where index_value = 19
INSERT INTO #xp_regread
exec master..xp_regread 'HKEY_LOCAL_MACHINE' ,'SYSTEM\CurrentControlSet\Control\ProductOptions','ProductSuite'
SELECT TOP 1 @OSType = index_value2 FROM #xp_regread
DELETE FROM #xp_regread
IF (ServerProperty('IsClustered') = 1 )
BEGIN
exec master..xp_regread 'HKEY_LOCAL_MACHINE' ,'SYSTEM\CurrentControlSet\Control\ComputerName\ActiveComputerName','ComputerName',@ActiveNode OUTPUT
exec master..xp_regread 'HKEY_LOCAL_MACHINE' ,'CLUSTER\Nodes\1','NodeName',@Node1 OUTPUT
exec master..xp_regread 'HKEY_LOCAL_MACHINE' ,'CLUSTER\Nodes\2','NodeName',@Node2 OUTPUT
SELECT @ActiveNode = @ActiveNode + ' (' + @Node1+ '/' +@Node2 +')'
END
exec master..xp_regread 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\Windows NT\CurrentVersion', 'CSDVersion', @OSRevision OUTPUT
exec master..xp_regread 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\Windows NT\CurrentVersion', 'CurrentVersion', @CurrentVersion OUTPUT
exec master..xp_regread 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\Windows NT\CurrentVersion', 'ProductName', @OSName OUTPUT
exec master..xp_regread 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\Windows NT\CurrentVersion\WinLogon', 'DefaultDomainName', @Domain OUTPUT
declare @ip varchar(40)
Declare @ipLine varchar(200)
Declare @pos int
Declare @pos2 int
set nocount on
set @ip = NULL
Create table #tempip (ipLine varchar(200))
Declare @SQLStmt nvarchar(100)
SET @SQLStmt = 'Insert #tempip exec master..xp_cmdshell ' + ''''+ 'ping -l 1 -n 1 ' + convert(varchar(20), ServerProperty('machineName') )+ ''''
exec sp_executesql @SQLStmt
select @ipLine = ipLine
from #tempip
where upper (ipLine) like '%PINGING%'
if (isnull (@ipLine,'***') != '***')
begin
set @pos = CharIndex ('[',@ipLine,1);
set @pos2 = CharIndex (']',@ipLine,1);
set @ip = rtrim(ltrim(substring (@ipLine , @pos + 1 , @pos2 - @pos - 1)))
end
SELECT
@Env Env,
CASE WHEN @ip like '10.49.%' THEN 'OLD PROD/QA'
WHEN @ip like '10.5.%' THEN 'QA'
WHEN @ip like '10.4.%' THEN 'PROD'
WHEN @ip like '10.6.%' THEN 'DURHAM'
WHEN @ip like '10.48.%' THEN 'Dev/Test'
ELSE 'Unknown IP'
END ENV_IP,
@AppName AppName,
@dba-2 DBA,
ServerProperty('ServerName') ServerName,
ServerProperty('MachineName') VirtualName,
ServerProperty('InstanceName') InstanceName,
isnull(@ip, '') IPAddress,
@@Servername SQLServerName,
ISNULL(@ActiveNode, convert(char(50), ServerProperty('MachineName'))) ActiveNode,
CASE WHEN ServerProperty('IsClustered') = 1 THEN 'Is Clustered' ELSE 'Not Clustered' END IsClustered,
@Domain Domain,
@OSName OSName,
@OSType OSType,
@CurrentVersion OSVersion,
@OSRevision OSRevision,
ServerProperty('Edition') SQLEdition,
ServerProperty('ProductLevel') SQLProductLevel,
ServerProperty('ProductVersion') SQLProductVersion,
@ProcessorCount ProcessorCount,
@PhysicalMemory PhysicalMemory,
convert(varchar(50), @MinSQLServerMemory) MinSQLServerMemory_MB,
convert(varchar(50), @MaxSQLServerMemory) MaxSQLServerMemory_MB,
CASE WHEN @AWEEnabled = 1 THEN 'AWE Enabled' ELSE 'AWE Disabled' END AWEEnabled,
CASE WHEN ServerProperty('IsFullTextInstalled') = 1 THEN 'Full-text is installed' ELSE 'Full-text is not installed' END IsFullTextInstalled,
--CASE WHEN ServerProperty('IsIntegratedSecurityOnly') = 1 THEN 'Integrated security' ELSE 'Not Integrated security' END IsIntegratedSecurityOnly,
ServerProperty('LicenseType') LicenseType,
convert(varchar(50), ServerProperty('NumLicenses')) NumLicenses,
convert(varchar(50), @MaxDegParallelism) MaxDegParallelism,
@SQLServerAgent SQLServerAgent,
@MSDTC MSDTC,
getdate() [Timestamp]
INTO SQL_Server_Env
DROP TABLE #xp_servicecontrol
DROP TABLE #xp_msver
DROP TABLE #xp_regread
DROP TABLE #tempip
GO
USE master
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SQL_Server_Devices]'))
drop table SQL_Server_Devices
DECLARE @dbname sysname,
@dbid varchar(10),
@SQLCmd NVARCHAR(4000),
@ActiveNode char (16),
@SQLServername varchar(50),
@dbowner varchar(50),
@cmptlevel varchar(10),
@Env varchar(50),
@AppName varchar(50),
@dba-2 varchar(50)
SELECT @Env = Env, @AppName = AppName, @dba-2 = DBA FROM DBA
SET @dbname = ' '
SET @dbname = (SELECT MIN(name) FROM master.dbo.sysdatabases)
SELECT @dbid = dbid, @dbowner = ISNULL(SUSER_Sname(sid),'NULL'), @cmptlevel = cmptlevel FROM master.dbo.sysdatabases WHERE name = @dbname
SET @SQLServername = convert(varchar(50), ServerProperty('Servername'))
exec master..xp_regread 'HKEY_LOCAL_MACHINE' ,'SYSTEM\CurrentControlSet\Control\Computername\ActiveComputername','Computername',@ActiveNode OUTPUT
CREATE TABLE #Databaseinfo
(
Environment varchar(50),
Applicationname varchar(50),
DBA varchar(50),
SQLServername varchar(50),
ActiveNode char (16),
[Database] sysname,
[DBID] varchar(10),
[DeviceType] VARCHAR (10),
[Filename] VARCHAR (30),
[FilePath] VARCHAR (100),
[size_MB] DECIMAL(15, 2),
[Filegrowth] int,
[FilegrowthType] VARCHAR(50),
[Autogrowth] VARCHAR(50),
dbOwner varchar(50),
cmptlevel varchar(10)
)
WHILE @dbname IS NOT NULL
BEGIN
SET @SQLCmd = 'SELECT
''' + @Env + ''' [Env],
''' + @AppName + ''' [AppName],
''' + @dba-2 + ''' [DBA],
''' + @SQLServername + ''' [SQLServername],
''' + @ActiveNode + ''' [ActiveNode],
''' + @dbname + ''' [Database],
''' + @dbid + ''' [DBID],
CASE WHEN sf.groupid = 0 THEN ''Log '' ELSE ''Data'' END AS [DeviceType]
,CAST(rtrim(sf.name) AS varchar(30))AS [Filename]
,CAST(sf.filename AS varchar(100))AS [FilePath]
,CAST(sf.size/128.0 AS DECIMAL(10,2)) AS [size_MB]
,CASE WHEN (sf.status & 0x100000) > 0 THEN LTRIM(STR(sf.growth)) ELSE STR(sf.growth/128) END AS [File growth]
,CASE WHEN (sf.status & 0x100000) > 0 THEN ''Percent'' ELSE ''MB'' END AS [FilegrowthType]
,CASE WHEN sf.growth = 0 THEN ''None'' ELSE ''Unrestricted'' END AS [Autogrowth]
,''' + @dbowner + ''' [DBOwner]
,''' + @cmptlevel + ''' [CmptLevel]
FROM [' + @dbname + ']..sysfiles sf left outer join sysfilegroups sfg
on sf.groupid = sfg.groupid
'
INSERT INTO #Databaseinfo
EXEC sp_executesql @SQLCmd
IF @dbname IS NULL BREAK
SELECT @dbname = MIN(name) FROM master.dbo.sysdatabases WHERE name > @dbname
SELECT @dbid = dbid, @dbowner = ISNULL(SUSER_Sname(sid),'NULL'), @cmptlevel = cmptlevel FROM master.dbo.sysdatabases WHERE name = @dbname
END
--SELECT * FROM #Databaseinfo
SELECT Environment,Applicationname, DBA,SQLServername, ActiveNode,[Database],DBID,DeviceType, [Filename],FilePath, convert(varchar(50),size_MB)size_MB,convert(varchar(50),[Filegrowth]) Filegrowth,FilegrowthType, Autogrowth,dbOwner, cmptlevel, getdate() [Timestamp]
INTO SQL_Server_Devices
FROM #Databaseinfo
DROP Table #Databaseinfo
GO
USE master
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SQL_Server_Drives]'))
drop table SQL_Server_Drives
DECLARE @hr int
DECLARE @fso int
DECLARE @drive char(1)
DECLARE @odrive int
DECLARE @TotalSize varchar(20)
DECLARE @Env varchar(50)
DECLARE @AppName varchar(50)
DECLARE @dba-2 varchar(50)
DECLARE @ActiveNode char (16)
DECLARE @MB bigint ; SET @MB = 1048576
SELECT @Env = Env, @AppName = AppName, @dba-2 = DBA FROM DBA
exec master..xp_regread 'HKEY_LOCAL_MACHINE' ,'SYSTEM\CurrentControlSet\Control\ComputerName\ActiveComputerName','ComputerName',@ActiveNode OUTPUT
CREATE TABLE #drives (ServerName varchar(50) NULL,
drive char(1) NULL,
FreeSpace int NULL,
TotalSize int NULL,
FreespaceTimestamp DATETIME NULL)
INSERT #drives(drive,FreeSpace)
EXEC master.dbo.xp_fixeddrives
EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR SELECT drive from #drives
ORDER by drive
OPEN dcur
FETCH NEXT FROM dcur INTO @drive
WHILE @@FETCH_STATUS=0
BEGIN
EXEC @hr = sp_OAMethod @fso,'Getdrive', @odrive OUT, @drive
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
EXEC @hr = sp_OAGetProperty @odrive,'TotalSize', @TotalSize OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @odrive
UPDATE #drives
SET TotalSize=@TotalSize/@MB, ServerName = @@servername, FreespaceTimestamp = (GETDATE())
WHERE drive=@drive
FETCH NEXT FROM dcur INTO @drive
END
CLOSE dcur
DEALLOCATE dcur
EXEC @hr=sp_OADestroy @fso
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
SELECT
@Env Env,
@AppName AppName,
@dba-2 DBA,
ServerName,
@ActiveNode ActiveNode,
drive,
CONVERT (varchar(50), TotalSize )as 'Total(MB)',
CONVERT (varchar(50), FreeSpace )as 'Free(MB)',
CONVERT (varchar(10), CAST((FreeSpace/(TotalSize*1.0))*100.0 as int)) as 'Free(%)',
CONVERT (varchar(50), FreespaceTimestamp) FreeTimestamp
into SQL_Server_Drives
FROM #drives
ORDER BY drive
DROP TABLE #drives
GO
USE master
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SQL_Server_Jobs]'))
drop table SQL_Server_Jobs
DECLARE
@job_id UNIQUEIDENTIFIER ,
@job_type VARCHAR(12) , -- LOCAL or MULTI-SERVER
@owner_login_name sysname ,
@subsystem NVARCHAR(40) ,
@category_id INT ,
@enabled TINYINT ,
@execution_status INT , -- 0 = Not idle or suspended, 1 = Executing, 2 = Waiting For Thread, 3 = Between Retries, 4 = Idle, 5 = Suspended, [6 = WaitingForStepToFinish], 7 = PerformingCompletionActions
@date_comparator CHAR(1) , -- >, < or =
@date_created DATETIME ,
@date_last_modified DATETIME ,
@description NVARCHAR(512),
@ServerId int,
@is_sysadmin INT,
@job_owner sysname,
@SQLStmt varchar(1000),
@Version decimal (2,1),
@Env varchar(50),
@AppName varchar(50),
@dba-2 varchar(50)
SELECT @Env = Env, @AppName = AppName, @dba-2 = DBA FROM DBA
SET @job_id = NULL
SET @job_type = NULL -- LOCAL or MULTI-SERVER
SET @owner_login_name= NULL
SET @subsystem = NULL
SET @category_id = NULL
SET @enabled = NULL
SET @execution_status = NULL -- 0 = Not idle or suspended, 1 = Executing, 2 = Waiting For Thread, 3 = Between Retries, 4 = Idle, 5 = Suspended, [6 = WaitingForStepToFinish], 7 = PerformingCompletionActions
SET @date_comparator = NULL -- >, < or =
SET @date_created = NULL
SET @date_last_modified = NULL
SET @description = NULL -- We do a LIKE on this so it can include wildcards
CREATE TABLE #xp_results (job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NULL,
requested_to_run INT NULL, -- BOOL
request_source INT NULL,
request_source_id sysname COLLATE database_default NULL,
running INT NULL, -- BOOL
current_step INT NULL,
current_retry_attempt INT NULL,
job_state INT NULL)
SELECT @is_sysadmin = ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0)
SELECT @job_owner = SUSER_SNAME()
INSERT INTO #xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner, @job_id
SELECT
@Env Env,
@AppName AppName,
@dba-2 DBA,
ServerProperty('ServerName') srvname,
sjs.database_name,
REPLACE(j.name, '&', 'and') job_name,
SUSER_SNAME(owner_sid) job_owner,
CASE WHEN j.enabled = 1 THEN 'Enabled' ELSE 'DISABLED' END enabled,
CASE WHEN js.last_run_outcome = 1 THEN 'Succeeded'
WHEN js.last_run_outcome = 0 THEN 'FAILED'
WHEN js.last_run_outcome = 3 THEN 'Cancelled'
WHEN js.last_run_outcome = 4 THEN 'InProgress'
WHEN js.last_run_outcome = 5 THEN 'Unknown'
ELSE 'Unknown' END last_run_outcome_desc,
CASE WHEN x.job_state = 4 THEN 'Idle'
WHEN x.job_state = 1 THEN 'Running'
WHEN x.job_state = 2 THEN 'BLOCKED'
WHEN x.job_state = 5 THEN 'SUSPENDED'
WHEN x.job_state = 3 THEN 'Retrying'
WHEN x.job_state = 6 THEN 'Waiting On Step'
WHEN x.job_state = 0 THEN 'Unknown'
WHEN x.job_state = 7 THEN 'Logging'
ELSE 'Unknown' END execution_status_desc,
SUBSTRING(convert(varchar(10), convert(decimal(10,6),js.last_run_duration/1000000.00)),3,2)+':'+
SUBSTRING(convert(varchar(10), convert(decimal(10,6),js.last_run_duration/1000000.00)),5,2)+':'+
SUBSTRING(convert(varchar(10), convert(decimal(10,6),js.last_run_duration/1000000.00)),7,2) last_run_duration,
CAST(js.last_run_date as varchar) as last_run_date,
LEFT(CASE WHEN LEN(convert(varchar(10),(js.last_run_time/10000))) = 1 THEN '0' + convert(varchar(10),(js.last_run_time/10000)) ELSE convert(varchar(10),(js.last_run_time/10000)) END + ':'
+ replace(convert(varchar(10),(convert(decimal(15,2) ,js.last_run_time)/10000)- (js.last_run_time/10000)),'0.',''), 5) last_run_time,
CAST(x.next_run_date as varchar) as next_run_date,
LEFT(CASE WHEN LEN(convert(varchar(10),(x.next_run_time/10000))) = 1 THEN '0' + convert(varchar(10),(x.next_run_time/10000)) ELSE convert(varchar(10),(x.next_run_time/10000)) END + ':'
+ replace(convert(varchar(10),(convert(decimal(15,2) ,x.next_run_time)/10000)- (x.next_run_time/10000)),'0.',''), 5) next_run_time ,
getdate() [Timestamp]
INTO SQL_Server_Jobs
FROM msdb.dbo.sysjobs j, msdb.dbo.sysjobservers js, #xp_results x, master.dbo.sysservers s, msdb.dbo.sysjobsteps sjs
WHERE j.job_id = js.job_id
AND j.job_id = x.job_id
AND s.srvid = js.server_id
AND sjs.step_id = 1
AND j.job_id = sjs.job_id
DROP TABLE #xp_results
set nocount off
GO
March 28, 2009 at 7:33 pm
[Quote]
you need to be very thorough
[/Quote]
You got to be kidding, right?
March 29, 2009 at 11:19 am
Hi ravinegi,
I think you should post the city/state/country your company is located.
Therewith you'd probably get contacted from one of the local consultants that follow this site or even participate.
The way your request is posted you're not looking for a hint on how to resolve an issue but for a "100%-tested-against-all-circumstances-error-free-full-functional-copy-and-paste solution".
Your request of being "very thorough" involves one simple thing: a contract.
If your question would have been "How can I figure out what portion of that script causes locking and blocking?" or "Is there a way to avoid the WHILE loop?" probably there already would have been some suggestions. But a "very thorough" tuning of 470 lines of code as a charity task is asking for a little too much - at least from my point of view.
March 29, 2009 at 11:42 am
Hi ravinegi
I don't want to comment the "very thorough" (I just can confirm the others).
If I overlook this claim there is another problem with your post. As Lutz already denoted you cannot post a 470 line script and says "this locks, please help". Your script contains many GOs so it will be executed in completely own steps. First you have to use the SQL Server Profiler to investigate which of these separate scripts causes the locks. After that execute only some parts of the identified script. If you found a specific part which causes the lock post this and request help. Maybe without the "claim"...
Greets
Flo
March 29, 2009 at 11:50 am
At the very least, put it in tags so that it keeps its formatting.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 29, 2009 at 12:44 pm
I tried it on my laptop and it finished in nothing flat. True, its just a laptop, but I do have over 30 databases.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply