Management Studio Find and Replace

July 8, 2010

Some pretty cool stuff…

Did you know that SQL Server Management Studio makes a nice little regular expression (regex) editor and tester?  Go to find and replace (Ctrl-H) and down at the bottom of the dialog box is a check box names “Use”.  Check that box and choose Regular Expression.  Now to the right of the box where you type the text, there is a glyph that will fly out a regular expression character helper.  You can type in text in Management Studio and test a regular expression against it.

You can also leverage this regular expression functionality to do a multi-line find and replace.  You can substitute in the old C# (C++) syntax \n for a new line.  I needed to replace a block of text with a multi-line block of text and this worked beautifully!

Don’t forget you can use Management Studio to search in files too.  A nice GREP tool too!  Hit the glyph just to the right of Quick Replace or Quick Find at the top and you’ll get the option to search in files.


Katmai (SQL 2008) IntelliSense

February 2, 2008

I wanted to put up a sample of what IntelliSense looks like it Katmai:


(Click on the image for full size)

This is a feature that we have all been hearing about and has finally arrived with Katmai.

I can see how this is going to be very helpful for developer productivity since developers will be prompted with the names of databases, tables, and columns without having to use the Object Explorer.

By the way – I’m using Windows Live Writer to compose this post.  I used it in the first version and this new version is very nice.  I think I’ll be using it from now on.  It facilitates creating more complex and better looking posts.  Very cool.

Fragmentation Stats on an Index (Part 2)

November 26, 2007

In my last post I was discussing how and why clicking on the Fragmentation tab on the properties page of an index could take a while to return results.  I identified it as a DBCC since sp_who2 as well as being identified as a DBCC command by querying

SELECT * FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text(sql_handle)

I guess the point I was trying to make was that index fragmentation stats don’t seem to be cached anywhere (why would they be since they are changing all the time) and must be calculated each time you click on the Fragmentation tab – or perform DBCC SHOWCONTIG – or query db_dm_physical_index_stats.  This means that on deep tables, these queries can take some time.  I’m not working with an amazing disk subsystem and the server I’m working on only has two processors.  So for me – for sure – they take a while.

If you don’t mind waiting for SSMS to finish the query to see the results, that’s great.  I had accidentally clicked on it and needed SSMS to release the thread so that that SSMS would operate properly.  I’m hoping that I was showing how to deal with SSMS hanging on this page when your data set is large.

Thanks to Simon Worth for pointing this out.  He is completely correct.  DBCC SHOWCONTIG has been retired for using db_dm_physical_index_stats instead.  SSMS is making a SELECT to db_dm_physical_index_stats and not really calling DBCC SHOWCONTIG.  I wouldn’t be suprised if under the covers that both DBCC SHOWCONTIG and a call to db_dm_physical_index_stats are doing the exact same operations to figure out what the fragmentation stats are.  Microsoft has just put a new front end on to DBCC SHOWCONTIG so that it looks more like a regular T-SQL SELECT statement.

Thanks, Simon.

Fragmentation Stats on an Index

November 8, 2007

I’m working with very large data sets and I accidentally clicked on the “Fragmentation” tab on the properties page for the clustered index of a table.  Management Studio did not handle this very gracefully.  Everything locked up and Management Studio becomes un-usable.

What Managment Studio is doing is issuing a DBCC on the index to see what the fragmentation stats are.  What makes this bad is that this table has 100,000,000 rows in it.  Needless to say, this takes a while to complete.

I decided that I wasn’t willing to wait for the DBCC to finish so I used a KILL statement to end the process.  Everything came back to life and I was able to dismiss the index properties dialog.

I wonder if it would be nice for SQL Server to have some idea if the DBCC will take a while and if it will, maybe it can be treaded off so that it doesn’t hang SSMS.  Just a thought.