Sep 302013
 

sqlsat190_speakingI must admit… Denver is my favorite town in this country. The outdoor things to do, the wide variety of locals and transplants, and the thin air all are favorable to me. I’m looking to move here someday soon. With that said, this year’s SQL Saturday #190 in Denver was absolutely wonderful, and not just because I love Denver! Denver has such an incredibly strong SQL Server community, and the warmth that I received from the attendees, other speakers, volunteers, and coordinators just re-enforces my desire to relocate (the SQL Server community in my town of Omaha is stellar, but just look at those mountains!). 

Golden CO

At this event, I presented an all new session entitled “Virtualizing Critical SQL Servers” that discussed how virtualization helps DBAs, how to incorporate key virtualization concepts into the daily SQL Server management routine, and how to build the SQL Server VM so you can get the most out of the environment. Most of the attendees had already virtualized most, if not all, of their SQL Servers. The comments and questions from the attendees in the packed room were very good, and I hope that this session helps tear down some of the organizational silos that we discussed and allows the teams to work more closely so that virtualization can really be embraced by the SQL Server teams and operational practices improved as a result!

The slides from my session are available for you to download here. Please feel free to download and review them, and contact me if you have any questions.

The next stop for me is Charleston’s next SQL Saturday and then the SQL PASS Summit! I look forward to seeing you all there!

Jun 132011
 

Ever want to do a bulk analysis of the possible numbers of using page or row compression on a SQL Server 2008(+) Enterprise database? Now you can – easily.

This script came from http://sqlfool.com/2011/06/estimate-compression-savings/, so thanks for the great script!

SET NOCOUNT ON; 

DECLARE @printOnly BIT = 0 — change to 1 if you don’t want to execute, just print commands
, @tableName VARCHAR(256)
, @schemaName VARCHAR(100)
, @sqlStatement NVARCHAR(1000)
, @tableCount INT
, @statusMsg VARCHAR(1000);

IF EXISTS(SELECT * FROM tempdb.sys.tables WHERE name LIKE ‘%#tables%’)
DROP TABLE #tables;

CREATE TABLE #tables
(
database_name sysname
, schemaName sysname NULL
, tableName sysname NULL
, processed BIT
);

IF EXISTS(SELECT * FROM tempdb.sys.tables WHERE name LIKE ‘%#compression%’)
DROP TABLE #compressionResults;

IF NOT EXISTS(SELECT * FROM tempdb.sys.tables WHERE name LIKE ‘%#compression%’)
BEGIN

CREATE TABLE #compressionResults
(
objectName VARCHAR(100)
, schemaName VARCHAR(50)
, index_id INT
, partition_number INT
, size_current_compression BIGINT
, size_requested_compression BIGINT
, sample_current_compression BIGINT
, sample_requested_compression BIGINT
);

END;

INSERT INTO #tables
SELECT DB_NAME()
, SCHEMA_NAME([schema_id])
, name
, 0 — unprocessed
FROM sys.tables;

SELECT @tableCount = COUNT(*) FROM #tables;

WHILE EXISTS(SELECT * FROM #tables WHERE processed = 0)
BEGIN

SELECT TOP 1 @tableName = tableName
, @schemaName = schemaName
FROM #tables WHERE processed = 0;

SELECT @statusMsg = ‘Working on ‘ + CAST(((@tableCount – COUNT(*)) + 1) AS VARCHAR(10))
+ ‘ of ‘ + CAST(@tableCount AS VARCHAR(10))
FROM #tables
WHERE processed = 0;

RAISERROR(@statusMsg, 0, 42) WITH NOWAIT;

SET @sqlStatement = ‘EXECUTE sp_estimate_data_compression_savings ”’
+ @schemaName + ”’, ”’ + @tableName + ”’, NULL, NULL, ”PAGE”;’ — ROW, PAGE, or NONE

IF @printOnly = 1
BEGIN

SELECT @sqlStatement;

END
ELSE
BEGIN

INSERT INTO #compressionResults
EXECUTE SP_EXECUTESQL @sqlStatement;

END;

UPDATE #tables
SET processed = 1
WHERE tableName = @tableName
AND schemaName = @schemaName;

END;

SELECT *
FROM #compressionResults;

Apr 062011
 

Need to kill all processes connected to a given database, say before a database restore where a web app is trying to reconnect?

 

DECLARE @SQL VARCHAR(8000) 

SELECT @SQL=COALESCE(@SQL,”)+’Kill ‘+CAST(spid AS VARCHAR(10))+ ‘; ‘

FROM sys.sysprocesses

WHERE DBID=DB_ID(‘AdventureWorks’)

EXEC(@SQL)

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close