SQL RNNR

1 DBA's Professional Blog

Categories: Scripts

Seldom Used Indexes

by Jason Brimhall

Categories: News, Professional, Scripts, SSC, SSSOLV

Tags: Index Maintenance, SQL Administration, SQL Script

Comments: 7 Comments

Published on: March 20, 2012

On occasion you may ask yourself if there are any under used indexes in your database. If not you, then possibly a manager or client. Usually this comes up when evaluating for missing indexes or better indexes.

SQL Server provides a means to find the information we seek. We can query the sys.dm_db_index_usage_stats DMV to garner much of the information we want. You can read about this DMV here.

The columns we want to take a look at are the seeks, scans and lookups columns. In conjunction with that, we want to compare those columns to the updates related columns.

Here is an example query.

Code block

SELECT TableName = OBJECT_NAME(s.OBJECT_ID),IndexName = i.name, i.index_id
        ,TotalUserWrites = user_updates
        ,TotalUserReads = user_seeks + user_scans + user_lookups
        ,[DIFFERENCE] = user_updates - (user_seeks + user_scans + user_lookups)
		,TotalSystemReads = s.system_seeks + s.system_scans + s.system_lookups
		,TotalSystemWrites = s.system_updates
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 (user_seeks + user_scans + user_lookups + s.system_seeks + s.system_scans + s.system_lookups) = 0
	AND i.index_id > 1
ORDER BY [DIFFERENCE] DESC, TotalUserWrites DESC, TotalUserReads ASC OPTION (RECOMPILE);

In this query, I am looking at two main components for comparison. I want to determine where there are more updates on an index than reads. I also want to see only those indexes that have no reads.

Using a query like this can help you to narrow your search for under-performing indexes. Indexes returned by this query tend to be more costly to maintain than the benefit they may be providing. Starting with the indexes returned by this query, you can test and confirm the findings.

Agent Jobs Using SSIS

by Jason Brimhall

Categories: News, Professional, Scripts, SSC, SSSOLV

Tags: SQL Administration, SQL Script, SSIS

Comments: No Comments

Published on: March 15, 2012

This is a short script to help the DBA with documentation purposes. This would come in handy especially in those cases where you are consulting or you have taken on a new job.

Suppose you want/need to find out what SQL Agent jobs are running SSIS packages. This script will help to quickly identify those jobs.

Code block

SELECT DISTINCT j.name
	FROM msdb.dbo.sysjobs j
		INNER JOIN msdb.dbo.sysjobsteps js
			ON j.job_id = js.job_id
	WHERE js.subsystem = 'SSIS'
		OR js.command LIKE '%DTEXEC.exe%'

Table Size and Missing FK Indexes

by Jason Brimhall

Categories: News, Professional, Scripts, SSC

Tags: Foreign Key, SQL Administration, SQL Script

Comments: No Comments

Published on: March 14, 2012

I am bringing an oldie back with another twist. I recently ran into the need to correlate information between a couple of different queries that I like to use. The two scripts returned different pieces of data about tables in a database. This information was table size and missing foreign key indexes.

I needed to combine the two queries due to a desire to create indexes on foreign keys based on table size. The premise behind this was to get the biggest bang for the buck initially as we work toward optimizing a database. We happened to know heading into this that some of the larger tables are the most heavily queried tables as well.

So, here is what I did to get that information quickly.

Code block

/* Part I */
--Drop Table #indstats
IF exists (SELECT * FROM tempdb.sys.objects WHERE name like '%#indstats%')
BEGIN
	DROP TABLE tempdb.dbo.#indstats
END
 
BEGIN
CREATE TABLE #indstats (
         IndStatsID INT PRIMARY KEY CLUSTERED
        ,database_id BIGINT
        ,index_id BIGINT
        ,IndexSizeMB DECIMAL(16,1)
        ,OBJECT_ID	BIGINT
);
END
 
INSERT INTO #indstats (IndStatsID,database_id,index_id,OBJECT_ID,IndexSizeMB)
        SELECT Row_Number() OVER (ORDER BY OBJECT_ID) AS IndStatsID
				,database_id,index_id,OBJECT_ID
				,CONVERT(DECIMAL(19,2),(SUM(ps.page_count))) * 8 /1024 AS IndexSizeMB
			FROM sys.dm_db_index_physical_stats(DB_ID(),null,NULL,NULL,'SAMPLED') ps
			GROUP BY database_id,OBJECT_ID,index_id;
 
/* Part II */
DECLARE @dbsize DECIMAL(19,2)
 
SET NOCOUNT ON
 
/*
**  Summary data.
*/
BEGIN
	SELECT @dbsize = SUM(CONVERT(DECIMAL(19,2),CASE WHEN type = 0 THEN SIZE ELSE 0 END)) * 8/1024
		FROM sys.database_files
 
END
 
/* Part III */
BEGIN
	WITH RegData AS (
		SELECT a.container_id,p.OBJECT_ID,p.index_id,us.database_id
			,FileGroupName = FILEGROUP_NAME(a.data_space_id)
			,TableName = OBJECT_NAME(p.OBJECT_ID)
			,NumRows = p.ROWS
			,UsedPages = IsNull(a.used_pages,0)
			,TotalPages = IsNull(a.total_pages,0)
			,DataSizeMB = CONVERT(DECIMAL(19,2),IsNull(a.used_pages,0)) * 8/1024
			,IndexSizeMB = CASE WHEN ps.index_id < 2 THEN 0 ELSE ps.IndexSizeMB END
			,UserRequests = IsNull(us.user_seeks,0) + IsNull(us.user_scans,0) + IsNull(us.user_lookups,0)
			,UserUpdates = IsNull(us.user_updates,0)
			,LastUpdate = IsNull(us.last_user_update,null)
			,RatioRequestsToUpdates = CAST(IsNull(us.user_seeks,0)
					+ IsNull(us.user_scans,0) + IsNull(us.user_lookups,0) AS REAL)
				/ CAST(CASE us.user_updates WHEN 0 THEN 1 ELSE us.user_updates END AS REAL)
		FROM sys.allocation_units a
			INNER Join sys.partitions p
				ON p.hobt_id = a.container_id
				And a.type = 1
			LEFT Outer Join sys.dm_db_index_usage_stats us
				ON us.OBJECT_ID = p.OBJECT_ID
				And us.index_id = p.index_id
				And us.database_id = DB_ID()
			LEFT Outer Join #indstats ps
				ON p.index_id = ps.index_id
				And ps.database_id = DB_ID()
				And p.OBJECT_ID = ps.OBJECT_ID
		--WHERE OBJECTPROPERTY(p.object_id,'IsMSShipped') = 0
	)
	, LOBData AS (
		SELECT a.container_id,p.OBJECT_ID,p.index_id,us.database_id
			,FileGroupName = FILEGROUP_NAME(a.data_space_id)
			,TableName = OBJECT_NAME(p.OBJECT_ID)
			,NumRows = p.ROWS
			,UsedPages = IsNull(a.used_pages,0)
			,TotalPages = IsNull(a.total_pages,0)
			,DataSizeMB = CONVERT(DECIMAL(19,2),IsNull(a.used_pages,0)) * 8/1024
			,IndexSizeMB = CASE WHEN ps.index_id < 2 THEN 0 ELSE ps.IndexSizeMB END
			,UserRequests = IsNull(us.user_seeks,0) + IsNull(us.user_scans,0) + IsNull(us.user_lookups,0)
			,UserUpdates = IsNull(us.user_updates,0)
			,LastUpdate = IsNull(us.last_user_update,null)
			,RatioRequestsToUpdates = CAST(IsNull(us.user_seeks,0)
					+ IsNull(us.user_scans,0) + IsNull(us.user_lookups,0) AS REAL)
				/ CAST(CASE us.user_updates WHEN 0 THEN 1 ELSE us.user_updates END AS REAL)
		FROM sys.allocation_units a
			INNER Join sys.partitions p
				ON p.partition_id = a.container_id
				And a.type = 2
			LEFT Outer Join sys.dm_db_index_usage_stats us
				ON us.OBJECT_ID = p.OBJECT_ID
				And us.index_id = p.index_id
				And us.database_id = DB_ID()
			LEFT Outer Join #indstats ps
				ON p.index_id = ps.index_id
				And ps.database_id = DB_ID()
				And p.OBJECT_ID = ps.OBJECT_ID
		--WHERE OBJECTPROPERTY(p.object_id,'IsMSShipped') = 0
	)
	, OverFlowData AS (
		SELECT a.container_id,p.OBJECT_ID,p.index_id,us.database_id
			,FileGroupName = FILEGROUP_NAME(a.data_space_id)
			,TableName = OBJECT_NAME(p.OBJECT_ID)
			,NumRows = p.ROWS
			,UsedPages = IsNull(a.used_pages,0)
			,TotalPages = IsNull(a.total_pages,0)
			,DataSizeMB = CONVERT(DECIMAL(19,2),IsNull(a.used_pages,0)) * 8/1024
			,IndexSizeMB = CASE WHEN ps.index_id < 2 THEN 0 ELSE ps.IndexSizeMB END
			,UserRequests = IsNull(us.user_seeks,0) + IsNull(us.user_scans,0) + IsNull(us.user_lookups,0)
			,UserUpdates = IsNull(us.user_updates,0)
			,LastUpdate = IsNull(us.last_user_update,null)
			,RatioRequestsToUpdates = CAST(IsNull(us.user_seeks,0)
					+ IsNull(us.user_scans,0) + IsNull(us.user_lookups,0) AS REAL)
				/ CAST(CASE us.user_updates WHEN 0 THEN 1 ELSE us.user_updates END AS REAL)
		FROM sys.allocation_units a
			INNER Join sys.partitions p
				ON p.hobt_id = a.container_id
				And a.type = 3
			LEFT Outer Join sys.dm_db_index_usage_stats us
				ON us.OBJECT_ID = p.OBJECT_ID
				And us.index_id = p.index_id
				And us.database_id = DB_ID()
			LEFT Outer Join #indstats ps
				ON p.index_id = ps.index_id
				And ps.database_id = DB_ID()
				And p.OBJECT_ID = ps.OBJECT_ID
		--WHERE OBJECTPROPERTY(p.object_id,'IsMSShipped') = 0
	), IndexSum AS (
		SELECT a.OBJECT_ID
		,AllDataSizeMB = SUM(CASE WHEN a.index_id in (0,1)
							THEN IsNull(a.DataSizeMB,0) +  IsNull(p2.DataSizeMB,0) +  IsNull(p3.DataSizeMB,0)
							ELSE IsNull(p2.DataSizeMB,0) + IsNull(p3.DataSizeMB,0)
							END)
		FROM RegData a
			LEFT Outer Join LOBData p2
				ON p2.container_id = a.container_id
			LEFT Outer Join OverFlowData p3
				ON p3.container_id = a.container_id
		GROUP BY a.OBJECT_ID
	), SummaryInfo AS (
		SELECT
			TableName = MAX(a.TableName)
			,InRowDataSizeMB = SUM(IsNull(a.DataSizeMB,0))
			,LOBDataSizeMB = SUM(IsNull(p2.DataSizeMB,0))
			,OFlowDataSizeMB = SUM(IsNull(p3.DataSizeMB,0))
			,NumRows = MAX(COALESCE(a.NumRows,p2.NumRows,p3.NumRows,0))
			,AllUsedPages = SUM(IsNull(a.UsedPages,0) + IsNull(p2.UsedPages,0) + IsNull(p3.UsedPages,0))
			,AllPages = SUM(IsNull(a.TotalPages,0) + IsNull(p2.TotalPages,0) + IsNull(p3.TotalPages,0))
			,FreeDataSpace = CONVERT(DECIMAL(19,2),
				SUM(IsNull(a.TotalPages,0) + IsNull(p2.TotalPages,0) + IsNull(p3.TotalPages,0))
				- SUM(IsNull(a.UsedPages,0) + IsNull(p2.UsedPages,0) + IsNull(p3.UsedPages,0)))* 8 / 1024
			,AllDataSizeMB = MAX(ids.AllDataSizeMB)
			,IndexSizeMB = SUM(IsNull(a.IndexSizeMB,0))
				+ SUM(IsNull(p2.IndexSizeMB,0)) + SUM(IsNull(p3.IndexSizeMB,0))
			,UserRequests = AVG(IsNull(a.UserRequests,0)
				+ IsNull(p2.UserRequests,0) + IsNull(p3.UserRequests,0))
			,UserUpdates = AVG(IsNull(a.UserUpdates,0) + IsNull(p2.UserUpdates,0) + IsNull(p3.UserUpdates,0))
			,LastUpdate = MAX(COALESCE(a.LastUpdate,p2.LastUpdate,p3.LastUpdate,null))
			,DatabaseSize = @dbsize
		FROM RegData a
			LEFT Outer Join LOBData p2
				ON p2.container_id = a.container_id
			LEFT Outer Join OverFlowData p3
				ON p3.container_id = a.container_id
			LEFT Outer Join sys.indexes i
				ON i.OBJECT_ID = a.OBJECT_ID
				And i.index_id = a.index_id
			LEFT Outer Join IndexSum ids
				ON i.OBJECT_ID = ids.OBJECT_ID
		GROUP BY a.OBJECT_ID
	), TotalUnused AS (
				SELECT SUM(FreeDataSpace) AS UnusedSpace
					FROM SummaryInfo
		)
	SELECT TableName,ChildTableColumn AS MissingFKIndexColumn,NumRows,InRowDataSizeMB,LOBDataSizeMB,OFlowDataSizeMB
			,AllUsedPages,AllPages
			,FreeDataSpace,AllDataSizeMB,IndexSizeMB
			,TableSizeMB = AllDataSizeMB + IndexSizeMB + FreeDataSpace
			,UserRequests,UserUpdates,LastUpdate
			,PercentofDB = ((IndexSizeMB + AllDataSizeMB) / DatabaseSize) * 100
			,DatabaseSize
			,DataUsedSize = DatabaseSize - TU.UnusedSpace
			,PercentofDataFileUsed = ((IndexSizeMB + AllDataSizeMB) / (DatabaseSize - TU.UnusedSpace)) * 100
		FROM SummaryInfo SI
			CROSS APPLY TotalUnused TU
			Cross Apply (SELECT
								OBJECT_NAME(FK.parent_object_id) AS FKtable
								,OBJECT_NAME(Fk.referenced_object_id) AS ReferencesThisTable
								,OBJECT_NAME(FK.constraint_object_id) AS FKName
								,C.name ChildTableColumn
							FROM sys.foreign_key_columns FK
								INNER JOIN sys.columns C
									ON FK.parent_object_id = C.OBJECT_ID
									AND FK.parent_column_id = C.column_id
								INNER JOIN sys.objects O
									ON FK.parent_object_id = o.OBJECT_ID
								LEFT OUTER JOIN sys.index_columns ic
									ON FK.parent_object_id = ic.OBJECT_ID
									AND FK.parent_column_id = ic.column_id
							WHERE ic.OBJECT_ID IS NULL) FK
		WHERE Fk.fktable = SI.TableName
		ORDER BY PercentofDB DESC
END

The change is not overly much. I added a subquery via a cross apply to get the missing foreign key info. Then I return the pertinent columns back to the Select query.

Physical or Virtual

by Jason Brimhall

Categories: News, Professional, Scripts, SSC, SSSOLV

Tags: PoSH, SQL Administration, SQL Script, TSQL

Comments: 2 Comments

Published on: March 7, 2012

Today I get to share something that has been on my someday list for quite some time. I have planned on getting a solution that involves TSQL to help determine if you are running SQL Server on a physical machine or if it happens to be a virtual machine. Now, I am prepared to share two means that involve using TSQL to achieve that goal.

I get to cover the spectrum with these two solutions. At one end, we have something that is relatively simple. It only works in SQL 2008 R2 and above though. At the other end of the spectrum, we get to use something that involves more of a sledgehammer.

Covering these two extremes is useful. As I said, the simple solution at the simple end of the spectrum is not going to work if you are running SQL Server 2008 or older. So, if you are running SQL 2005, for example, you would need something a bit less delicate. I will leave it to you to determine if it is worth it to use the “sledgehammer” approach.

Sledgehammer

I’m calling this the sledgehammer approach because it is not a 100% TSQL solution. Some may not like the idea due to the use of xp_cmdshell – making the sledgehammer more like a bull in a china shop.

Up front, this solution utilizes tools that are readily available. Those tools are PoSH, WMI and TSQL.

Let’s first look at the WMI. I knew I could find the information I wanted if I could query WMI. All that was needed was a means to get to the Win32_ComputerSystem class. From there I could get the information for manufacturer and model for the machine. Virtual machines tend to have a manufacturer such as the the following “VMWare”, “innotek”, and “Microsoft Corporation” – to list some of the more popular options.

Next, we can get to the WMI very easily via PoSH or vbscript. I was having a devil of a time trying to figure out a sane method of doing it via TSQL only. That is fine, because I was able to utilize PoSH quite nicely in this case. After, having found a reference for what I wanted to do from here, I was able to create the script that I needed. Here is that script.

Code block

Param ([string]$HostName="localhost",$Option=1)
 
function ExecQry {
    param ([string]$CmdStr)
         $cmdStr
    }
 
if ($Option=1){
    $x = ExecQry "gwmi Win32_ComputerSystem -computer `$hostname" | iex
    $x | fl manufacturer,model
 
}

Nothing too terribly fancy there. I am only querying for the two attributes that I really want to accomplish my goal. Those attributes being: manufacturer and model.

Next comes the difficult part. Running all of this from within TSQL and capturing useful results. If you execute that PoSH script, you will notice that the presentation of the results is really lacking. First though I needed to get the script to execute from within SSMS.

I started out miserably with getting that to run. The script just kept hanging and would never even cancel out. This is what I started out with in trying to get it to run.

Code block

xp_cmdshell 'powershell -noexit "& ""C:\Admin\PoSH Scripts\GetMachineInfo2.ps1"""'

If I extracted the powershell command and ran it from a command prompt, it would run flawlessly. From within SSMS – crash and burn every time. Then, I decided to try removing the -noexit and see if I could get different results. Voila – fantastic results. I now had the basis for getting this running in SSMS.

In addition to the use of xp_cmdshell, I felt it prudent to use a string splitter to help tidy up the presentation. The string splitter I like to use (delimited split function) can be found here. I also felt it necessary to use Pivot – again to help tidy up the results in the presentation.

I know, you’re itching to see the script now, so here it is.

Code block

DECLARE @processing TABLE (ID INT PRIMARY KEY CLUSTERED IDENTITY(1,1),Results VARCHAR(256))
DECLARE @PartTwo TABLE (ID INT, subID INT, Somevalue NVARCHAR(100))
 
DECLARE @PathtoPS1			VARCHAR(256)
		,@CommandtoExec		VARCHAR(512)
 
SET @PathtoPS1 = 'your file system path\GetMachineInfo2.ps1'
SET @CommandtoExec = 'powershell "& ""' + @PathtoPS1 +'"""'
;
 
INSERT INTO @processing(Results)
	EXEC xp_cmdshell @CommandtoExec
 
;
 
INSERT INTO @PartTwo (ID,subID,Somevalue)
	SELECT pr.ID,ss.ItemNumber,LTRIM(RTRIM(ss.Item)) AS Somevalue
		FROM @processing pr
		CROSS APPLY AdminDB_Test.dbo.stringsplitter(pr.Results,':') ss
 
;
SELECT Manufacturer,Model
		,CASE
			WHEN Manufacturer like '%Vmware%'
				THEN 'ISVIRTUAL'
			WHEN Manufacturer like '%innotek GmbH%'
				THEN 'ISVIRTUAL'
			WHEN Manufacturer like '%Microsoft Corporation%' AND Model like '%Virtual%'
				THEN 'ISVIRTUAL'
			ELSE 'PhysicalMachine'
			END AS VirtualorPhysical
	FROM (SELECT pt.SomeValue AS Property,p2.somevalue AS PropertyVal
			FROM @PartTwo pt
				INNER Join @PartTwo p2
					ON pt.id = p2.id
					And pt.subid <> p2.subid
			WHERE pt.somevalue in ('manufacturer','model')
			) B
	PIVOT (MIN(PropertyVal) FOR Property IN (manufacturer,model)) AS P

Unless you have xp_cmdshell disabled, the only change you will need to make is for the @PathtoPS1 variable. Save the PoSH script on your file system with the name GetMachineInfo2.ps1, and you will be all set.

Elegance

Now that you have seen the hard way of doing it, here is what we can do in SQL Server 2008 R2 (must have SP1 applied at a minimum – thanks to Nic Cain for that info).

Code block

SELECT @@SERVERNAME AS SRVName,virtual_machine_type
	FROM sys.dm_os_sys_info

The virtual_machine_type attribute is a new addition to this DMV as of SQL 2008 R2. There are three possible values: 0,1, or 2. The value of 0 means that the machine is physical. Any other value means that it is a virtual machine. You can read more about that from MSDN.

There you have it. Two methods within SSMS that you can extrapolate where a Server is physical or virtual.

Views in Use?

by Jason Brimhall

Categories: News, Professional, Scripts, SSC, SSSOLV

Tags: SQL Administration, SQL Script, TSQL

Comments: No Comments

Published on: March 6, 2012

Today, I am following up on a topic I mentioned in an earlier post. In case you haven’t read the post on finding your linked servers, you can read it here.

It is in that post that I spoke of altering a script I had just written for another client for another requirement. The requirements were very similar in nature.

The client needed to discover which, if any, stored procedures in the database referenced any of the thousands of views that had been created. (Spoiler – not a single view was being used by any stored procedure.)

My options at the time were to either write something that could do the work for me (rapidly) or manually investigate every stored procedure (there were thousands of those too). I opted for the faster approach.

I already had a script handy to search for particular words or key phrases throughout all of the procs. So, all I needed to do was work out the routine to search for all of the views. The only approach I felt would work across the board was to write a cursor to do it. For a job such as this, I think the tool is still appropriate.

Since you have already been exposed to a variation of the script, here is the version that works great for searching for the view usage in stored procs. This script is simpler in nature than the one I shared in the Linked Server article – only because I do not query that SQL Agent jiobs for ad-hoc queries that use any views.

Code block

DECLARE @VName VARCHAR(256)
DECLARE FindView CURSOR
LOCAL STATIC FORWARD_ONLY READ_ONLY
     FOR
SELECT name
	FROM sys.objects
	WHERE type = 'v'
		And is_ms_shipped = 0
 
OPEN FindView;
FETCH NEXT FROM FindView INTO @VName;
 
WHILE @@FETCH_STATUS = 0
BEGIN
	SELECT OBJECT_NAME(OBJECT_ID)
		FROM sys.sql_modules
		WHERE Definition LIKE '%'+@VName +'%'
		AND OBJECTPROPERTY(OBJECT_ID, 'IsProcedure') = 1 ;
 
	FETCH NEXT FROM findview INTO @VName;
END
 
CLOSE FindView
DEALLOCATE FindView

Put this in your toolbox if you like. It did take it quite a while to run (20 or so minutes) when I ran it against a database with thousands of views and thousands of procs. Just be warned .

Are my Linked Servers Being Used?

by Jason Brimhall

Categories: News, Professional, Scripts, SQLServerPedia Syndication, SSC

Tags: SQL Administration, SQL Script, TSQL

Comments: No Comments

Published on: March 5, 2012

This is a follow up to an article published on 3/1/2012. That article showed how to find what linked servers were created on your instance of SQL Server. You can read it here.

This article came about due to a request to find if any stored procedures are using any of the linked servers. In addition to finding if any stored procedures may be using a linked server was the need to find the name of that procedure. The request evolved to also include finding any SQL Agent jobs that may be using the linked server.

In response to that request, I had the idea to adapt a query I had recently written. I will be posting that in the near future.

Query

Code block

DECLARE @VName VARCHAR(256)
DECLARE Findlinked CURSOR
LOCAL STATIC FORWARD_ONLY READ_ONLY
     FOR
SELECT name AS name
	FROM sys.servers
	WHERE is_linked = 1
 
OPEN Findlinked;
FETCH NEXT FROM Findlinked INTO @VName;
 
WHILE @@FETCH_STATUS = 0
BEGIN
	SELECT OBJECT_NAME(OBJECT_ID)
		FROM sys.sql_modules
		WHERE Definition LIKE '%'+@VName +'%'
		AND OBJECTPROPERTY(OBJECT_ID, 'IsProcedure') = 1 ;
 
	FETCH NEXT FROM Findlinked INTO @VName;
END
CLOSE Findlinked
 
OPEN Findlinked;
FETCH NEXT FROM Findlinked INTO @VName;
 
WHILE @@FETCH_STATUS = 0
BEGIN
	SELECT j.name AS JobName,js.command
		FROM msdb.dbo.sysjobsteps js
			INNER JOIN msdb.dbo.sysjobs j
				ON j.job_id = js.job_id
		WHERE js.command LIKE '%'+@VName +'%'
	FETCH NEXT FROM Findlinked INTO @VName;
END
 
CLOSE Findlinked
DEALLOCATE Findlinked

Don’t blast me just yet due to the use of a loop (cursor). In a case such as what has just been presented, a cursor is a legitimate tool. I need to find all stored procedures that contain the text I specify. In this case, I am searching for each of the linked servers.

In addition to searching all of the stored procedures for the use of a defined linked server, I am re-opening the same cursor to search all of the jobs defined on the server. I do this in the event that the job was created with an ad-hoc query in lieu of using a stored procedure.

If you run the query, you will find that it should provide a quicker turnaround time on documenting the use of the linked servers than manually searching.

Linked Servers

by Jason Brimhall

Categories: News, Professional, Scripts, SSC, SSSOLV

Tags: SQL Administration, SQL Script, TSQL

Comments: No Comments

Published on: March 1, 2012

Do you have linked servers that have been created in your environment? Do you know what these linked servers are?

Finding Linked Servers

SQL Server has provided us means to be able to find the linked servers on each installed instance. I will show two simple means (that don’t involve traversing the SSMS GUI) that will list the linked servers.

Both methods are very similar in nature. I will leave it up to you to determine which you prefer to use.

Stored Procedure

Microsoft has shipped a stored procedure called sp_linkedservers. Execute this stored procedure and you will get a list of linked servers and the details related to those objects. One problem with this method is that not all the results returned by this proc truly represent linked servers. This procedure will also return the name of the instance to which you are also connected.

Catalog Query

This is really what the stored procedure is doing. But here is a modified query to return only those servers that are linked servers in the catalog.

Code block

SELECT SRV_NAME = srv.name,
        SRV_PROVIDERNAME    = srv.provider,
        SRV_PRODUCT         = srv.product,
        SRV_DATASOURCE      = srv.data_source,
        SRV_PROVIDERSTRING  = srv.provider_string,
        SRV_LOCATION        = srv.location,
        SRV_CAT             = srv.CATALOG
	FROM sys.servers srv
	WHERE is_linked = 1

The main difference being the where condition. Querying the sys.servers catalog with a condition of is_linked = 1 will return only those servers that are linked servers. Getting this information is a pre-requisite for an upcoming post. I hope you find this information useful.

A Trio of Functions

by Jason Brimhall

Categories: News, Professional, Scripts, SSC

Tags: Scripts, Security, TSQL

Comments: No Comments

Published on: January 17, 2012

I found myself perusing an execution plan the other day. I know, big surprise there. This execution plan showed me some interesting things I had never really paid much attention to in the past. When I started paying attention to these things, I found myself jumping down a rabbit hole.

It all started with a bit of curiosity to see if I could make an “admin” script perform a bit better. The execution plans started showing some table valued functions that I knew I hadn’t included in the query. Subsequently, I found myself wondering – what is that?

The items that made me curious were all table valued functions. There were three of them (different) in this particular plan. I started looking hither and thither to find these functions. It didn’t take long to figure out that I could find them in the mssqlsystemresource database. So I proceeded to making a copy of the database and attaching a copy of it for further learning opportunities.

The three functions are:

SYSSESSIONS

FNGETSQL

SYSCONNECTIONS

Knowing the query and based on these names, I began looking in the appropriate DMOs to see what I could find. Here are the scripts for each of those DMO’s.

Code block

USE [mssqlsystemresource_test]
GO
 
/****** Object:  View [sys].[dm_exec_connections]    Script Date: 01/16/2012 22:39:32 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE VIEW [sys].[dm_exec_connections] AS
	SELECT *
	FROM OPENROWSET(TABLE SYSCONNECTIONS)
 
GO
 
/****** Object:  View [sys].[dm_exec_sessions]    Script Date: 01/16/2012 22:39:37 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE VIEW [sys].[dm_exec_sessions] AS
	SELECT *
	FROM OPENROWSET(TABLE SYSSESSIONS)
 
GO
 
/****** Object:  UserDefinedFunction [sys].[dm_exec_sql_text]    Script Date: 01/16/2012 22:39:55 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE FUNCTION [sys].[dm_exec_sql_text](@handle VARBINARY(64))
RETURNS TABLE
AS
	RETURN SELECT * FROM OPENROWSET(TABLE FNGETSQL, @handle)
 
GO

Cool. I can now see the internals of each of the DMOs – sort of. You see, there is an OPENROWSET call in each of these objects. Each call uses an undocumented feature called TABLE. This is an internal command used by the engine and you won’t find much on it (mostly people asking what it is and Microsoft saying they won’t tell).

Here is the fun part. If you try to run that code outside of querying the DMO, you will receive error messages. If you try to create a new view utilizing the Openrowset, it will fail. It is reserved for internal usage. With that said, just continue to use the DMO and you will be fine. Personally, I was curious to find out how it worked so I tried a bit to find it.

So there you have it. If you are curious what is the internal makings of these DMOs, you can script them from the resource database. Alternatively, you could also run sp_helptext. I like to check these things from the resource database. It feels more like an adventure. Have fun with it and see what you will learn.

Database Data and Log Size Info

by Jason Brimhall

Categories: News, Professional, Scripts, SSC

Tags: Scripts, TSQL

Comments: 5 Comments

Published on: December 5, 2011

Have you ever wanted or needed to get the data and log file size for all of the databases in your server?

I have seen scripts that would get this information in the past. All of the scripts seemed to use either a cursor or the proc from Microsoft called sp_MSforeachdb. Having seen some of these scripts, I wondered if there might be a different way of doing it (that was also different than you see in my TableSpace Scripts).

After putting some thought into it, I decided on a plan of attack and went to work on building a query that would do the trick. But before I continue, I will advise that running a script like this on large databases may take a while to execute.

Keeping these things in mind (potential slowness when run on large db and wanting to try something different), I came up with this method (tested on Instances with small databases as well as Instances with 2TB Databases).

Code block

DECLARE @MaxDataSize	INT
SELECT @MaxDataSize = MAX(d.cntr_value)/1024/1024 --Convert to GB
    FROM sys.dm_os_performance_counters d
    WHERE d.OBJECT_NAME like '%databases%'
       and d.counter_name = 'Data File(s) Size (KB)'
    GROUP BY d.counter_name,d.OBJECT_NAME
 
DECLARE
@DbSize TABLE (Database_Id INT, DataUsedMB FLOAT)
 
IF @MaxDataSize > 250 --If Largest DB is >250GB then use forEachdb, else use Physical Index Stats
BEGIN
	INSERT INTO @DbSize (Database_Id,DataUsedMB)
	EXEC sp_MSforeachdb
	'select db_id(''?'') , (SUM(ps.reserved_page_count)*8)/1024 from ?.sys.dm_db_partition_stats ps'; 
 
	SELECT
        DB_ID(instance_name) Database_Id
        , instance_name Database_Name
        ,CAST(pvt.[DATA FILE(s) SIZE (KB)] AS FLOAT)/1024 AS DataFileSizeMB
        ,isnull(ca.DataUsedMB,.1) AS DataUsedMB
        ,(CAST(pvt.[DATA FILE(s) SIZE (KB)] AS FLOAT)/1024) - isnull(ca.DataUsedMB,.1) AS DataFreeMB
        , CASE WHEN pvt.[DATA FILE(s) SIZE (KB)] = 0
			THEN 0
			ELSE ROUND((((CAST(pvt.[DATA FILE(s) SIZE (KB)] AS FLOAT)/1024) - isnull(ca.DataUsedMB,.1)) / (CAST(pvt.[DATA FILE(s) SIZE (KB)] AS FLOAT)/1024))*100, 2)
			END DataFreePercent
        ,CAST(pvt.[LOG FILE(s) SIZE (KB)] AS FLOAT)/1024 AS LogFileSizeMB
        ,CAST(pvt.[LOG FILE(s) Used SIZE (KB)] AS FLOAT)/1024 AS LogFileUsedMB
        ,(CAST(pvt.[LOG FILE(s) SIZE (KB)] AS FLOAT) - pvt.[LOG FILE(s) Used SIZE (KB)])/1024 LogFreeSizeMB
        ,pvt.[PERCENT LOG Used] AS LogUsedPercent
        ,100-pvt.[PERCENT LOG Used] AS LogFreePercent
    FROM sys.dm_os_performance_counters d
    PIVOT (SUM(cntr_value) FOR counter_name IN ([DATA FILE(s) SIZE (KB)],[LOG FILE(s) SIZE (KB)],[LOG FILE(s) Used SIZE (KB)],[PERCENT LOG Used])) pvt
    LEFT Outer Join (SELECT Database_Id,Null AS page_count,DataUsedMB FROM @DbSize) CA
		ON (ca.database_id = DB_ID(instance_name))
    WHERE OBJECT_NAME like '%databases%'
		and pvt.[DATA FILE(s) SIZE (KB)] IS not null
		And pvt.[LOG FILE(s) SIZE (KB)] IS not null
		And pvt.[LOG FILE(s) Used SIZE (KB)] IS not null
		And pvt.[PERCENT LOG Used] IS not null
		and instance_name <> '_Total'
    ORDER BY Database_ID
END
ELSE
BEGIN
SELECT
        DB_ID(instance_name) Database_Id
        , instance_name Database_Name
        ,CAST(pvt.[DATA FILE(s) SIZE (KB)] AS FLOAT)/1024 AS DataFileSizeMB
        ,isnull(ca.DataUsedMB,.1) AS DataUsedMB
        ,(CAST(pvt.[DATA FILE(s) SIZE (KB)] AS FLOAT)/1024) - isnull(ca.DataUsedMB,.1) AS DataFreeMB
        , CASE WHEN pvt.[DATA FILE(s) SIZE (KB)] = 0
			THEN 0
			ELSE ROUND((((CAST(pvt.[DATA FILE(s) SIZE (KB)] AS FLOAT)/1024) - isnull(ca.DataUsedMB,.1)) / (CAST(pvt.[DATA FILE(s) SIZE (KB)] AS FLOAT)/1024))*100, 2)
			END DataFreePercent
        ,CAST(pvt.[LOG FILE(s) SIZE (KB)] AS FLOAT)/1024 AS LogFileSizeMB
        ,CAST(pvt.[LOG FILE(s) Used SIZE (KB)] AS FLOAT)/1024 AS LogFileUsedMB
        ,(CAST(pvt.[LOG FILE(s) SIZE (KB)] AS FLOAT) - pvt.[LOG FILE(s) Used SIZE (KB)])/1024 LogFreeSizeMB
        ,pvt.[PERCENT LOG Used] AS LogUsedPercent
        ,100-pvt.[PERCENT LOG Used] AS LogFreePercent
    FROM sys.dm_os_performance_counters d
    PIVOT (SUM(cntr_value) FOR counter_name IN ([DATA FILE(s) SIZE (KB)],[LOG FILE(s) SIZE (KB)],[LOG FILE(s) Used SIZE (KB)],[PERCENT LOG Used])) pvt
    LEFT Outer Join (SELECT ips.database_id,SUM(page_count) AS page_count,CAST((SUM(page_count)*8) AS FLOAT)/1024 AS DataUsedMB
		FROM sys.dm_db_index_physical_stats(NULL,NULL,NULL,NULL,'sampled') ips
		GROUP BY ips.database_id
		) CA
		ON (ca.database_id = DB_ID(instance_name))
    WHERE OBJECT_NAME like '%databases%'
		and pvt.[DATA FILE(s) SIZE (KB)] IS not null
		And pvt.[LOG FILE(s) SIZE (KB)] IS not null
		And pvt.[LOG FILE(s) Used SIZE (KB)] IS not null
		And pvt.[PERCENT LOG Used] IS not null
		and instance_name <> '_Total'
    ORDER BY Database_ID
END

You will see that I did not entirely eliminate the looping mechanism. Truth be told, it is so much faster on servers with Large Databases.

Also take note of the DMV that is in use in this query. I am taking advantage of the performance monitor stats that are exposed through the DMV sys.dm_os_performance_counters. One caveat to this method, is that this DMV shows us the size of the resource database as well. I think it is fine to report that information back – but it won’t change much over time. It is for that purpose that I use the Left Join in the query.

The other trick that I utilized is to Pivot that performance monitor data. I think this works better than to write a bunch of sub-queries to generate the same sort of data layout.

You have probably also noted that I have chosen 250GB as the tipping point in this query. There is no particular reason for that size – just a large enough database size to make the point. For some, the tipping point may be a much smaller database size. Feel free to change this value to suit your needs.

Some other caveats. The perfmon cntr_value data is presented as an Int. For more accurate math operations, I chose to cast many of these to a Float.

I also ran into a case where the perfmon data was reporting 0 as the Data File Size for one of my databases. I had to throw a case statement in for the Data File Used Percent in order to avoid a Divide by 0 error.

I also wrapped the DataUsedMB in an ISNULL. This was due to the model and mssqlsystemresource databases not having data in the dm_db_physical_stats function. I could have left those as NULL, but wanted to show something for them both.

Check it out and Enjoy!

Table Space – CS Part Deux

by Jason Brimhall

Categories: News, Professional, Scripts, SSC

Tags: Documentation, SQL Script, Table Info, TSQL

Comments: No Comments

Published on: November 21, 2011

Another script, another day. And as promised, I am providing an update to the Table Space script that followed the sp_MStableSpace script. Not a lot more to be said about this one, since much was said already.

I updated the script for those CS collations. I also provided the update to show the % of DB column to be driven based on the data file usage information.

Code block

/* Part I */
--Drop Table #indstats
IF exists (SELECT * FROM tempdb.sys.objects WHERE name like '%#indstats%')
BEGIN
	DROP TABLE tempdb.dbo.#indstats
END
 
BEGIN
CREATE TABLE #indstats (
         IndStatsID INT PRIMARY KEY CLUSTERED
        ,database_id BIGINT
        ,index_id BIGINT
        ,IndexSizeMB DECIMAL(16,1)
        ,OBJECT_ID	BIGINT
);
END
 
INSERT INTO #indstats (IndStatsID,database_id,index_id,OBJECT_ID,IndexSizeMB)
        SELECT Row_Number() OVER (ORDER BY OBJECT_ID) AS IndStatsID
				,database_id,index_id,OBJECT_ID
				,CONVERT(DECIMAL(19,2),(SUM(ps.page_count))) * 8 /1024 AS IndexSizeMB
			FROM sys.dm_db_index_physical_stats(DB_ID(),null,NULL,NULL,'DETAILED') ps
			GROUP BY database_id,OBJECT_ID,index_id;
 
/* Part II */
DECLARE @dbsize DECIMAL(19,2)
 
SET NOCOUNT ON
 
/*
**  Summary data.
*/
BEGIN
	SELECT @dbsize = SUM(CONVERT(DECIMAL(19,2),CASE WHEN type = 0 THEN SIZE ELSE 0 END)) * 8/1024
		FROM sys.database_files
 
END
 
/* Part III */
BEGIN
	WITH RegData AS (
		SELECT a.container_id,p.OBJECT_ID,p.index_id,us.database_id
			,FileGroupName = FILEGROUP_NAME(a.data_space_id)
			,TableName = OBJECT_NAME(p.OBJECT_ID)
			,NumRows = p.ROWS
			,UsedPages = IsNull(a.used_pages,0)
			,TotalPages = IsNull(a.total_pages,0)
			,DataSizeMB = CONVERT(DECIMAL(19,2),IsNull(a.used_pages,0)) * 8/1024
			,IndexSizeMB = CASE WHEN ps.index_id < 2 THEN 0 ELSE ps.IndexSizeMB END
			,UserRequests = IsNull(us.user_seeks,0) + IsNull(us.user_scans,0) + IsNull(us.user_lookups,0)
			,UserUpdates = IsNull(us.user_updates,0)
			,LastUpdate = IsNull(us.last_user_update,null)
			,RatioRequestsToUpdates = CAST(IsNull(us.user_seeks,0)
					+ IsNull(us.user_scans,0) + IsNull(us.user_lookups,0) AS REAL)
				/ CAST(CASE us.user_updates WHEN 0 THEN 1 ELSE us.user_updates END AS REAL)
		FROM sys.allocation_units a
			INNER Join sys.partitions p
				ON p.hobt_id = a.container_id
				And a.type = 1
			LEFT Outer Join sys.dm_db_index_usage_stats us
				ON us.OBJECT_ID = p.OBJECT_ID
				And us.index_id = p.index_id
				And us.database_id = DB_ID()
			LEFT Outer Join #indstats ps
				ON p.index_id = ps.index_id
				And ps.database_id = DB_ID()
				And p.OBJECT_ID = ps.OBJECT_ID
		--WHERE OBJECTPROPERTY(p.object_id,'IsMSShipped') = 0
	)
	, LOBData AS (
		SELECT a.container_id,p.OBJECT_ID,p.index_id,us.database_id
			,FileGroupName = FILEGROUP_NAME(a.data_space_id)
			,TableName = OBJECT_NAME(p.OBJECT_ID)
			,NumRows = p.ROWS
			,UsedPages = IsNull(a.used_pages,0)
			,TotalPages = IsNull(a.total_pages,0)
			,DataSizeMB = CONVERT(DECIMAL(19,2),IsNull(a.used_pages,0)) * 8/1024
			,IndexSizeMB = CASE WHEN ps.index_id < 2 THEN 0 ELSE ps.IndexSizeMB END
			,UserRequests = IsNull(us.user_seeks,0) + IsNull(us.user_scans,0) + IsNull(us.user_lookups,0)
			,UserUpdates = IsNull(us.user_updates,0)
			,LastUpdate = IsNull(us.last_user_update,null)
			,RatioRequestsToUpdates = CAST(IsNull(us.user_seeks,0)
					+ IsNull(us.user_scans,0) + IsNull(us.user_lookups,0) AS REAL)
				/ CAST(CASE us.user_updates WHEN 0 THEN 1 ELSE us.user_updates END AS REAL)
		FROM sys.allocation_units a
			INNER Join sys.partitions p
				ON p.partition_id = a.container_id
				And a.type = 2
			LEFT Outer Join sys.dm_db_index_usage_stats us
				ON us.OBJECT_ID = p.OBJECT_ID
				And us.index_id = p.index_id
				And us.database_id = DB_ID()
			LEFT Outer Join #indstats ps
				ON p.index_id = ps.index_id
				And ps.database_id = DB_ID()
				And p.OBJECT_ID = ps.OBJECT_ID
		--WHERE OBJECTPROPERTY(p.object_id,'IsMSShipped') = 0
	)
	, OverFlowData AS (
		SELECT a.container_id,p.OBJECT_ID,p.index_id,us.database_id
			,FileGroupName = FILEGROUP_NAME(a.data_space_id)
			,TableName = OBJECT_NAME(p.OBJECT_ID)
			,NumRows = p.ROWS
			,UsedPages = IsNull(a.used_pages,0)
			,TotalPages = IsNull(a.total_pages,0)
			,DataSizeMB = CONVERT(DECIMAL(19,2),IsNull(a.used_pages,0)) * 8/1024
			,IndexSizeMB = CASE WHEN ps.index_id < 2 THEN 0 ELSE ps.IndexSizeMB END
			,UserRequests = IsNull(us.user_seeks,0) + IsNull(us.user_scans,0) + IsNull(us.user_lookups,0)
			,UserUpdates = IsNull(us.user_updates,0)
			,LastUpdate = IsNull(us.last_user_update,null)
			,RatioRequestsToUpdates = CAST(IsNull(us.user_seeks,0)
					+ IsNull(us.user_scans,0) + IsNull(us.user_lookups,0) AS REAL)
				/ CAST(CASE us.user_updates WHEN 0 THEN 1 ELSE us.user_updates END AS REAL)
		FROM sys.allocation_units a
			INNER Join sys.partitions p
				ON p.hobt_id = a.container_id
				And a.type = 3
			LEFT Outer Join sys.dm_db_index_usage_stats us
				ON us.OBJECT_ID = p.OBJECT_ID
				And us.index_id = p.index_id
				And us.database_id = DB_ID()
			LEFT Outer Join #indstats ps
				ON p.index_id = ps.index_id
				And ps.database_id = DB_ID()
				And p.OBJECT_ID = ps.OBJECT_ID
		--WHERE OBJECTPROPERTY(p.object_id,'IsMSShipped') = 0
	), IndexSum AS (
		SELECT a.OBJECT_ID
		,AllDataSizeMB = SUM(CASE WHEN a.index_id in (0,1)
							THEN IsNull(a.DataSizeMB,0) +  IsNull(p2.DataSizeMB,0) +  IsNull(p3.DataSizeMB,0)
							ELSE IsNull(p2.DataSizeMB,0) + IsNull(p3.DataSizeMB,0)
							END)
		FROM RegData a
			LEFT Outer Join LOBData p2
				ON p2.container_id = a.container_id
			LEFT Outer Join OverFlowData p3
				ON p3.container_id = a.container_id
		GROUP BY a.OBJECT_ID
	), SummaryInfo AS (
		SELECT
			TableName = MAX(a.TableName)
			,InRowDataSizeMB = SUM(IsNull(a.DataSizeMB,0))
			,LOBDataSizeMB = SUM(IsNull(p2.DataSizeMB,0))
			,OFlowDataSizeMB = SUM(IsNull(p3.DataSizeMB,0))
			,NumRows = MAX(COALESCE(a.NumRows,p2.NumRows,p3.NumRows,0))
			,AllUsedPages = SUM(IsNull(a.UsedPages,0) + IsNull(p2.UsedPages,0) + IsNull(p3.UsedPages,0))
			,AllPages = SUM(IsNull(a.TotalPages,0) + IsNull(p2.TotalPages,0) + IsNull(p3.TotalPages,0))
			,FreeDataSpace = CONVERT(DECIMAL(19,2),
				SUM(IsNull(a.TotalPages,0) + IsNull(p2.TotalPages,0) + IsNull(p3.TotalPages,0))
				- SUM(IsNull(a.UsedPages,0) + IsNull(p2.UsedPages,0) + IsNull(p3.UsedPages,0)))* 8 / 1024
			,AllDataSizeMB = MAX(ids.AllDataSizeMB)
			,IndexSizeMB = SUM(IsNull(a.IndexSizeMB,0))
				+ SUM(IsNull(p2.IndexSizeMB,0)) + SUM(IsNull(p3.IndexSizeMB,0))
			,UserRequests = AVG(IsNull(a.UserRequests,0)
				+ IsNull(p2.UserRequests,0) + IsNull(p3.UserRequests,0))
			,UserUpdates = AVG(IsNull(a.UserUpdates,0) + IsNull(p2.UserUpdates,0) + IsNull(p3.UserUpdates,0))
			,LastUpdate = MAX(COALESCE(a.LastUpdate,p2.LastUpdate,p3.LastUpdate,null))
			,DatabaseSize = @dbsize
		FROM RegData a
			LEFT Outer Join LOBData p2
				ON p2.container_id = a.container_id
			LEFT Outer Join OverFlowData p3
				ON p3.container_id = a.container_id
			LEFT Outer Join sys.indexes i
				ON i.OBJECT_ID = a.OBJECT_ID
				And i.index_id = a.index_id
			LEFT Outer Join IndexSum ids
				ON i.OBJECT_ID = ids.OBJECT_ID
		GROUP BY a.OBJECT_ID
	), TotalUnused AS (
				SELECT SUM(FreeDataSpace) AS UnusedSpace
					FROM SummaryInfo
		)
	SELECT TableName,NumRows,InRowDataSizeMB,LOBDataSizeMB,OFlowDataSizeMB
			,AllUsedPages,AllPages
			,FreeDataSpace,AllDataSizeMB,IndexSizeMB
			,TableSizeMB = AllDataSizeMB + IndexSizeMB + FreeDataSpace
			,UserRequests,UserUpdates,LastUpdate
			,PercentofDB = ((IndexSizeMB + AllDataSizeMB) / DatabaseSize) * 100
			,DatabaseSize
			,DataUsedSize = DatabaseSize - TU.UnusedSpace
			,PercentofDataFileUsed = ((IndexSizeMB + AllDataSizeMB) / (DatabaseSize - TU.UnusedSpace)) * 100
		FROM SummaryInfo SI
			CROSS APPLY TotalUnused TU
		ORDER BY PercentofDB DESC
END

Phew, I finally took care of some of those somedays that have been nagging me. Sure, there has been a someday that has evolved due to that – but that is a good thing.

It helps that I also need these scripts to be CS. Add to that, that I need to use them more frequently and it was a perfect opportunity to do a little housecleaning.

page 1 of 3»

May 2012
M	T	W	T	F	S	S
« Apr
	1	2	3	4	5	6
7	8	9	10	11	12	13
14	15	16	17	18	19	20
21	22	23	24	25	26	27
28	29	30	31

Content

Now Reading

Planned books:

Throne of Fire, the by Rick Riordan
Seeds of Rebellion (Beyonders) by Brandon Mull

Current books:

ChiRunning: A Revolutionary Approach to Effortless, Injury-Free Running by Danny Dreyer, Katherine Dreyer
Advanced Marathoning – 2nd Edition by Peter Pfitzinger, Scott Douglas
SQL Server MVP Deep Dives by Nielsen Paul, Delaney Kalen, Machanic Adam, Tripp Kimberly, Randal Paul, Low Greg
A World Without Heroes (Beyonders) by Brandon Mull

Recent books:

The Red Pyramid (The Kane Chronicles, Book 1) by Rick Riordan
Fablehaven, Book 5:Keys to the Demon Prison by Brandon Mull
Catching Fire (The Second Book of the Hunger Games) by Suzanne Collins
Pirate Latitudes by Michael Crichton
The Battle of the Labyrinth (Percy Jackson and the Olympians, Book 4) by Rick Riordan

View full Library

SQL RNNR

Seldom Used Indexes

Agent Jobs Using SSIS

Table Size and Missing FK Indexes

Physical or Virtual

Sledgehammer

Elegance

Views in Use?

Are my Linked Servers Being Used?

Query

Linked Servers

Finding Linked Servers

Stored Procedure

Catalog Query

A Trio of Functions

Database Data and Log Size Info

Table Space – CS Part Deux

Categories

Popular Posts

Blogroll

Now Reading

Planned books:

Current books:

Recent books:

SQLHelp