NTSSUG June Presentation

Here are the slides and scripts from my June presentation at the North Texas SQL Server Users Group. The presentation was on the Data Collector in SQL Server 2008 but it was short so I also touched on filtered indexes, query hashes and troubleshooting. Enjoy and let me know if you have any questions.

Also Peter Debetta is presenting TONIGHT at 6:30 on the following topics:

"We'll be discussing the various ways you can secure your SQL Server data via encryption, including the new Transparent Data Encryption feature of SQL Server 2008."

 Stop by.

email it! |  |   |   |   |   | 

Permalink     0 Comments

Stupid XML SSMS Trick

This might be HAWTALTA for some of you. I just thought of doing this today even though I have been using xml showplan since I was knee high to a junebug. You can make query results clickable just by casting them as XML. For example:

select cast('select 1' as xml)

By clicking the resulting "select 1", a new query editor window opens with "select 1" ready to execute. This would be useful for scripts that generate scripts. Here is a practical example. (Forgive the formatting. I cannot get too wide in the post.)

SELECT  sys.objects.name

, (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) as Impact

,  cast('CREATE NONCLUSTERED INDEX ~NewNameHere~ ON ' 

+ sys.objects.name + ' ( ' + mid.equality_columns + 

CASE WHEN mid.inequality_columns IS NULL THEN '' 

ELSE CASE WHEN mid.equality_columns IS NULL 

THEN '' ELSE ',' END + mid.inequality_columns END + ' ) ' 

+ CASE WHEN mid.included_columns IS NULL 

THEN '' ELSE 'INCLUDE (' + mid.included_columns + ')' 

END + ';' as xml) AS CreateIndexStatement

, mid.equality_columns

, mid.inequality_columns

, mid.included_columns

FROM sys.dm_db_missing_index_group_stats AS migs 

INNER JOIN sys.dm_db_missing_index_groups AS mig 

ON migs.group_handle = mig.index_group_handle 

INNER JOIN sys.dm_db_missing_index_details AS mid 

ON mig.index_handle = mid.index_handle 

INNER JOIN sys.objects WITH (nolock) 

ON mid.object_id = sys.objects.object_id

WHERE     (migs.group_handle IN

(SELECT TOP (500) group_handle

FROM  sys.dm_db_missing_index_group_stats WITH (nolock)

ORDER BY (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) DESC))

ORDER BY 2 DESC 

Here are what some of the results look like.

Update : You also have to associate the XML file extension with the SQL editor to be executable.

email it! |  |   |   |   |   | 

Permalink     2 Comments

Reminder: SSWUG Virtual Conference Starts Tomorrow

Just a quick reminder that the SSWUG virtual conference starts tomorrow. I hope you can attend. They have given me a discount code to distribute: VIPJM2008DIS I do not get any extra cha-ching from you using it. I just state that so this post doesn't sound spammy.

Actually hearing my recorded voice makes me cringe so this will definitely be interesting for me. :)

Here are the sessions I am doing:

Filtered Indexes and Statistics in SQL 2008

SQL Reporting Services for the DBA

SQL Profiler: Configuration, Analysis, and SQL Server 2008 Enhancements

http://vconferenceonline.com/sswug/demo.asp

email it! |  |   |   |   |   | 

Permalink     0 Comments

Attn DFW SQL'ers

Reminder: You can catch me at the North Texas SQL Server User's Group tomorrow night. The Data Collection presen