[Index] DMV to create your missing indexes

This is pure genius.

http://blogs.msdn.com/bartd/archive/2007/07/19/are-you-using-sql-s-missing-index-dmvs.aspx

Uses the DMV to create missing indexes.

(update 2013/05/10 and something, in retrospect, you need to be careful about - don't just add everything it shows.  I was reading that MS actually had this in a CTP, where it would tell you the indexes to add, like Bart's script below.  And people wrote code that would automatically add them, without thinking about what other similar indexes were there, what the impact would be on inserts and updates, etc.  So they caused massive performance problems.  Practical upshot: be careful)

SELECT
mig.index_group_handle, mid.index_handle,
CONVERT (decimal (28,1),
migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)
) AS improvement_measure,
'CREATE INDEX missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle)
+ ' ON ' + mid.statement
+ ' (' + ISNULL (mid.equality_columns,'')
+ CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END
+ ISNULL (mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,
migs.*, mid.database_id, mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE CONVERT (decimal (28,1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

[Documentation] Now where are my databases again?

Everyone probably already has a version of this, but I saw some horrid code for it the other day. The below is probably 2005 specific (obviously, because of sys.master_files). Master.sys.master_files is very nice - keeps track of all the databases in one spot, rather than needing to query each databases' table.

USE master
go
SELECT sysdatabases.NAME, mf.* FROM sys.master_files mf
INNER JOIN sysdatabases ON mf.database_id = sysdatabases.dbid
ORDER BY physical_name

[Shopping] Results of my pressure cooker expedition

So, I got one. Fagor Duo. In retrospect, the Splendid would work fine, since very few recipes call for 7psi - everyone uses 15. And while some things work wonderfully well, it's not a panacea.

For one, most of the recipes are things you could've done in a crockpot. (Although maybe that's a plus...)

Secondly, while some things work wonderfully, others don't. I have a beef stew (I'll put the recipe up, even though it's not baking) that normally needs to cook for 40 minutes. With the pressure cooker, it cooks for 20... but requires 5 minutes to get up to temp, and 15 to cool down (better results). So the same time, and I have to clean the pressure cooker. Some things work well... but is cooking something in 10 minutes that much better than something in 20?

However, the beef stew _does_ gain the following:
1) Even more tender
2) Flavor better
3) The potatoes get cooked with the meat, so I don't have to deal with boiling potatoes or scrubbing that pot to remove the starch.

[Documentation] List your backups in Wiki format

Something simple I cooked up for documentation. Basically, it looks at the backups made in the past 2 weeks and create a table (in Wiki format). Cut and paste into your wiki page, and done.

BTW - if anyone has code to easily POST this to a web site, I'd be grateful. Ideally I'd set this up to run weekly and show changes in the environment.

Cut and paste the code - Wiki tables require that spacing. Make sure to run in TEXT mode

And yes, I know. The code is crude.

CREATE TABLE #wikilist (id INT IDENTITY, formatting VARCHAR(300))

INSERT INTO #wikilist (formatting)
SELECT distinct 'Server: ' + server_name + '{BR}{| border="1"
! Database !! Type !! Location'
FROM msdb.dbo.backupmediafamily backupmediafamily
inner join msdb.dbo.backupset backupset
on backupset.media_set_id = backupmediafamily.media_set_id
where backup_start_date > getdate()-14

INSERT INTO #wikilist (formatting)
--SELECT * FROM
select distinct '|-
| ' +
database_name + ' || ' +
CASE backupset.[type]
WHEN 'D' then 'Database'
WHEN 'I' THEN 'Differential database'
WHEN 'L' THEN 'TLog'
WHEN 'F' THEN 'File or filegroup'
WHEN 'G' THEN 'Differential file'
WHEN 'P' THEN 'Partial'
WHEN 'Q' THEN 'Differential partial'
END + ' || ' +
lower(left(physical_device_name,(len(rtrim(physical_device_name)) - charindex('\',reverse(rtrim(physical_device_name)))))) AS Location
from msdb.dbo.backupmediafamily backupmediafamily
inner join msdb.dbo.backupset backupset
on backupset.media_set_id = backupmediafamily.media_set_id
where backup_start_date > getdate()-14
and left(physical_device_name,(len(rtrim(physical_device_name)) - charindex('\',reverse(rtrim(physical_device_name))))) not like '%:'
GROUP BY '|-
| ' +
database_name + ' || ' +
CASE backupset.[type]
WHEN 'D' then 'Database'
WHEN 'I' THEN 'Differential database'
WHEN 'L' THEN 'TLog'
WHEN 'F' THEN 'File or filegroup'
WHEN 'G' THEN 'Differential file'
WHEN 'P' THEN 'Partial'
WHEN 'Q' THEN 'Differential partial'
END + ' || ' +
lower(left(physical_device_name,(len(rtrim(physical_device_name)) - charindex('\',reverse(rtrim(physical_device_name))))))
ORDER BY '|-
| ' +
database_name + ' || ' +
CASE backupset.[type]
WHEN 'D' then 'Database'
WHEN 'I' THEN 'Differential database'
WHEN 'L' THEN 'TLog'
WHEN 'F' THEN 'File or filegroup'
WHEN 'G' THEN 'Differential file'
WHEN 'P' THEN 'Partial'
WHEN 'Q' THEN 'Differential partial'
END + ' || ' +
lower(left(physical_device_name,(len(rtrim(physical_device_name)) - charindex('\',reverse(rtrim(physical_device_name))))))

INSERT INTO #wikilist (formatting) VALUES ('|}')
SELECT formatting FROM #wikilist ORDER BY id

[Linked Servers] Trick to making cross-server queries faster

Learned this years ago, and it's one of those nice tricks to keep in your cap.

So, what collation are you running? Do you know? Do you always use the default? Do you use linked servers and need a little more performance?

If you do, then you're in luck. If you have a linked server between two servers running the same collation, enable "collation compatible" and your queries will run faster.

Why? As I remember, if you don't have it enabled, then your query is sent across without the WHERE clause. Once it comes back, it's evaluated, ensuring that collation is properly dealt with. If you have collation compatible = true, then it sends over the whole query, including the WHERE clause. So, fewer results returned, lower I/O on the far-side, and no processing required locally.

One thing, though - make sure you're on the same collation. On 2005, the default is still the same (IIRC), but what it's called has changed.

[Replication] Document what tables are replicated

I needed this, and rather than do it the easy/long way once, I wrote a piece of code to do it from now on. Feel free to modify it, and if you can get that inner loop to create the #Articles table dynamically, PLEASE let me know. I also decided to use the SPs to try and keep it somewhat portable, and because I wanted to play with the prior post (saving SPs to tables).

Oh yeah, what this does: finds each replicated database, then walks each publication and get a list of the articles in that publication. Then formats it for a wiki, using bullets
(aka
* item 1
* item 2).

You'll have to enable data access on your local machine:
EXEC sp_serveroption [your_server_name] , 'data access' , 'true'

CREATE TABLE #Articles
(
[article id] INT,
[article name] sysname,
[base object] nvarchar(257),
[destination object] sysname null,
[synchronization object] nvarchar(257),
[type] SMALLINT,
[status] TINYINT,
filter nvarchar(257),
[description] nvarchar(255),
insert_command nvarchar(255),
update_command nvarchar(255),
delete_command nvarchar(255),
[creation script path] nvarchar(255),
[vertical partition] BIT,
pre_creation_cmd TINYINT,
filter_clause NTEXT,
schema_option binary(8),
dest_owner sysname null,
source_owner sysname null,
unqua_source_object sysname null,
sync_object_owner sysname null,
unqualified_sync_object sysname null,
filter_owner sysname null,
unqua_filter sysname null,
auto_identity_range INT,
publisher_identity_range INT,
identity_range BIGINT,
threshold BIGINT,
identityrangemanagementoption INT,
fire_triggers_on_snapshot BIT
)

CREATE TABLE #finished_list (id INT IDENTITY(1,1), names VARCHAR(100))

DECLARE
@minid SMALLINT,
@maxid SMALLINT,
@min_database SMALLINT,
@max_database SMALLINT,
@min_publication SMALLINT,
@max_publication SMALLINT,
@sqlstatement VARCHAR(1000),
@db_name sysname,
@publication_name sysname

SELECT identity(int, 1,1) as ID, name INTO #Databases FROM MASTER.dbo.sysdatabases WHERE category > 0 AND NAME <> 'distribution'
SELECT @min_database = MIN(id), @max_database = MAX(id) FROM #Databases

--outer loop - each database that has publications
WHILE @min_database <= @max_database
BEGIN
SET @db_name = null
SELECT @db_name = NAME FROM #Databases WHERE id = @min_database
IF @min_database = 1
SELECT @sqlstatement = 'SELECT identity(int, 1,1) as ID, * INTO ##Publications FROM OPENQUERY( [your_server],''SET FMTONLY OFF {call ' + @db_name + '..sp_helppublication}'')'
ELSE SELECT @sqlstatement = 'insert INTO ##Publications exec ' + @db_name + '..sp_helppublication'
EXEC(@sqlstatement)
INSERT INTO #finished_list (names) select '* ' + @db_name + ''
SELECT @min_publication = MIN(id), @max_publication = MAX(id) FROM ##Publications
--inner loop - each particular article
WHILE @min_publication <= @max_publication
BEGIN
SET @publication_name = NULL
SELECT @publication_name = NAME FROM ##publications WHERE id = @min_publication
INSERT INTO #finished_list (names) SELECT '** ' + @publication_name + ''
SELECT @sqlstatement = 'INSERT INTO #Articles EXEC ' + @db_name + '..sp_helparticle @publication = ''' + @publication_name + ''''
--I can't get this to work - if you can, please share. Probably something involving sp_executesql
-- IF @min_publication = 1
-- SELECT @sqlstatement = 'SELECT identity(int, 1,1) as ID, * INTO ##Articles FROM OPENQUERY( [your_server],''SET FMTONLY OFF {call ' + @db_name + '..sp_helparticle @publication = N''''' + @publication_name +'''''}'')'
-- ELSE SELECT @sqlstatement = 'insert into ##articles EXEC ' + @db_name + '..sp_helparticle @publication = N'''+ @publication_name +''' '
EXEC(@sqlstatement)
INSERT INTO #finished_list (names) SELECT '*** ' + [article name]+ '' FROM #articles ORDER BY [article name]
truncate table #articles
SET @min_publication = @min_publication + 1
END

TRUNCATE TABLE ##publications
SET @min_database = @min_database+1
END

SELECT * FROM #finished_list ORDER BY id
DROP TABLE #Databases
DROP TABLE ##Publications
DROP TABLE #finished_list
DROP TABLE #articles


SELECT INTO from SP

Found this on usenet, courtesy of BP Margolin (who pointed at a post by Umachandar Jayachandran). I've always just figured out what the columns are and built a table, but this will save me a ton of work.

-- To enable data access locally, do:
EXEC sp_serveroption localsrvr , 'data access' , 'true'
--(where localsrvr is your server's name)

-- To do SELECT...INTO from SPs results do:
SELECT * INTO SomeTbl FROM OPENQUERY(localsrvr , '{call sp_who}')

-- If the SP uses temporary tables,
-- you have to do something like the following b'cos
-- the above call will fail
SELECT * INTO SomeTbl FROM OPENQUERY( localsrvr ,'SET FMTONLY OFF {call sp_who}')

But you need to understand that this will result in the SP being executed twice once when OPENQUERY tries to determine the metadata for the result set & again
when it actually executes the call.


[Shopping] Buying a pressure cooker

Mostly for my benefit, but maybe it'll help someone else.

Why a pressure cooker?

Easy - time. Cooks things much faster. Boiling point of water in a pressure cooker (at 15psi) is a mere 250 degrees. Which means you can boil things in 2/3rds the time. And some other impressive things - a 6-hour stock in an hour.

Rules for pressure cookers

1. Don't low-ball the price. You're talking about something that will hold boiling liquids/food at pressure. As in bike-tire pressures. The last thing you want is for it to give - it can hurt you, maim you, and at the very least make a mess of the kitchen that will be Epic.
   
2. Same goes for used. Be safe on something like this.
   
3. If it feels cheap, it probably is. You want heft.
   
4. You want Stainless Steel, not aluminum. Aluminum will pit and hold gunk within - not a happy thing.
   
5. 3-layer bottoms. Makes it cook more evenly.
   
6. Bigger is better, you need room for the steam to build pressure. Supposed 6 quarts is the magic number.
   
7. Get a modified-first-gen (pressure valve) or second-gen (spring valve), not a "jiggle top".
   
8. Expect to spend between $70 and $250.
   

Brands I've seen recommended: Fagor (budget pick), Kuhn Rikon ("mercedes of pressure cookers"), Magefesa, and WMF (brand used by Alton Brown, but man that's pricey).

references:
http://missvickie.com/workshop/buying.html
http://www.realfoodliving.com/KuhnRikon.htm
http://query.nytimes.com/gst/fullpage.html?res=980DE5D61E3CF93BA15750C0A9679C8B63&sec=&spon=&pagewanted=all

My decision:
Either the Fagor or the Kuhn Rikon. Fagor's about $50 less, but I think either will be a good choice. The way I figure it, I cheaped out once on a different piece of cookware (a cast-iron skillet), and I might as well throw it out... tried seasoning it for years, and it still has yet to taste as good as the Lodge Logic we bought.

[Sysadmin] Kick users after midnight

Not mine, but we use it. Pretty basic - looks for SPIDs that are in a particular database and kills the SPIDs. We use it to ensure that people don't have active connections during maintenance time.

The one downside is that it's very literal - if you aren't explicitly in the system as sysadmin, out you go. Set in a job to run right before your maintenance.


set nocount on
declare @spid nvarchar(10)
declare @killem nvarchar(20)
declare spid_csr insensitive cursor for
select spid from master..sysprocesses
where sid not in
(
select sid from master..syslogins
where sysadmin = 1
)
and dbid in (db_id('Main'),db_id('AnotherOne'))
and loginame like 'MyDomain%'
open spid_csr
fetch next from spid_csr into @spid
while @@fetch_status = 0
begin
select @killem = 'kill ' + convert(varchar(3),@spid)
exec (@killem)
fetch next from spid_csr into @spid
end
close spid_csr
deallocate spid_csr

[Baking] Chicago Deep Dish Pizza

This weekend I expanded my repertoire, and made pizza. While I love Gino's & Due's in Chicago, getting them shipped is _pricey_.
I used the following recipe I found on the net.
http://reviewboard.com/articles_ektid256.aspx
Well, that's nifty - the site has changed owners. Thank goodness for the Internet Wayback Machine, which had a copy.

The Best Deep Dish Pizza

by Philip Ferreira
Best Deep Dish Pizza Background

When I was a kid I used to work at a place in Chicago that made some of the best Italian food that I ever ate. One of their specialties was Chicago Deep Dish Pizza. Now that I live on the East Coast I find myself wishing more and more that I had access to that pizza. Not that I can't make it, but because like anything it is a chore and it is easier to pick up the phone and order a few for the family.

Alas I am the only person in my house that knows how to do it so the chore gets put on me fairly regularly. Normally I don't give up my secrets without a fight, but living on the East Coast has made me sympathetic to the people that are not in Chicago who do not have access to the great pizza. That being said here I go:
Best Deep Dish Pizza Recipe:

You'll be able to make two good sized pizzas with this recipe. It takes about 3 hours so make sure you have everything you need before you start. This recipe is expensive, probably comparable to what it would cost to buy them. The ingrediants need to be fresh, and if you substitute or don't do something the way I tell you to, don't blame me for the results. Making Authentic Deep Dish Pizza is an art, it took me a long time to get it right.
Best Deep Dish Pizza - What you need to start:

You will need an electric mixer with a dough hook. If you don't have one you can try and follow along by kneeding the dough yourself (You will need to add another 45 minutes to this process if you kneed the dough).

Make sure you have the following ready:

2 18" deep dish pizza pans - Don't use a baking dish, go out and splurge on a few pans, this is serious pizza and you shouldn't go screwing it up by trying to make it in a 9x15 baking dish it won't cook right, it won't be the same and you will probably end up thinking this recipe stinks.

2 Tablespoons of Sugar (Needs to be sugar, the yeast feeds on it and won't proof without it).
4 Cups of Warm Water (110 degrees when you pour it in the bowl it will cool by the time you get everything else done)
4 Packages of Yeast or if you have the jar you can do 8 teaspoons of Yeast.
1 cup of First Press REALLY GOOD Extra Virgin olive oil.
1 Cup of Yellow Cornmeal
9 Cups of Flour (Up to 10)
Best Deep Dish Pizza - Technique

Proofing the yeast is an important part of this process. Make sure you do it right, you want your bowl of water to be about 95 - 100 degrees. Mix in the 2 tablespoons of sugar and stir it with a wisk. Once you disolve the sugar in the water, put the yeast in and make sure it all gets wet. (Yeast tends to float on the top and some of it won't proof if you don't wet it). Now walk away from it for about 10 minutes. It should be in a big bowl because this stuff is going to FOAM up and it will spill over if you don't have a big enough bowl - you have been warned ;)

In your mixer mix the olive oil, the cornmeal and 5 cups of flour. Mix it up for about 1 minute and add the yeast slowly while it is mixing up. Slowly add the rest of the flour and let the mixer mix on about 1/3 speed for 5 minutes. The dough should not be sticky or wet, it should feel like really soft smooth elastic. Coat a plastic bowl with a little olive oil and put the dough into the bowl (Big bowl). Cover the top of it with a damp towel and let it rise until it is double the size.

Punch it down and let it rise again.
Best Deep Dish Pizza - Prep Your Pans First!

Prep your pizza pans, spread a little olive oil on the surface and sides and sprinkle yellow cornmeal on the bottom of it. This will prevent the pizza from sticking to the pan. Alternately (and I do it this way quite a bit) you can take REAL butter and really give it a good coating all away around and in the pan. Layer it on very thick. It gives the crust an amazing flavor. Sprinkle with yellow cornmeal the same way you would if you used olive oil.
Best Deep Dish Pizza - Mix Your Cheeses and Make Your Sauce

Make your sauce & cheese mixes while you are waiting around for the dough to rise. Here is what you do:

Cheese Mix:
4 Pounds of Grated Mozzarella
1 Pound of Provolone
1 Pound of Romano, Parmigiano, Asiago mix (You can get them predone at the store in the deli section)

Mix the cheese mix in a big bowl so it is blended well.

Sauce:
4 28 oz Cans of Plum Tomatoes, Drain them and then put them through your blender for about 10 - 15 seconds you want them to be crushed up and chunky, but not liquid.
5 Teaspoons of FRESH Chopped up Basil
5 Teaspoons of FRESH Chopped Oregano
2 Tablespoons of Sugar (Or Splenda, I use Splenda)
10 Cloves of FRESH Garlic Peeled and Crushed with a Garlic Press
Salt and Pepper to taste
1/2 cup of Parmigiano Cheese Grated

Combine the ingrediants together and make sure it has good time to sit and steep in the acids from the tomatoes. This will bring out the flavors of the seasonings.
Best Deep Dish Pizza - Bringing Everything Together.

Once your dough has doubled again take it out and divide it into two sections with a knife. Roll it out onto your deep dish pizza pans (last chance to go out and buy pizza pans if you are using a baking dish you will not get the consistancy you need and you will not be happy). When you are laying the dough onto the pan push the dough to the edge. You can then turn the pan slowly while you pull the dough up the sides. If you have extra dough (and you should) roll it flat put it on a cookie sheet mist it with some olive oil, sprinkle it with garlic powder, Parmigiano, a little salt and bake it with your pizzas. You slice it with your pizza cutter after it's baked and dip it the left over pizza sauce. Presto free pizza bread / bread sticks.

Now that you have your pizza dough ready take a brush and brush olive oil onto the dough. Add your toppings (I use Portabella Mushrooms, Italian Sausage, Pepperoni, Green Pepper, Onion and Black Olives.) on the dough, then put half of your cheese mix on one pizza, half on the other. Use it all!

Pour your sauce on top until it reaches the edge of the dough (which should be all the way up the side of the pan), spread it out evenly and sprinkle with Parmigiano. Note: For all you folks that have never had Chicago Deep Dish Pizza, the sauce is on TOP so it's a red top pizza. This is traditional, and trust me it is good!
Best Deep Dish Pizza - Heat Your Oven and Make Sure You Let It Cool!

Preheat your oven and bake for 25 minutes on 350 degrees. After 25 minutes crank the oven up to 475 and bake for another 10 - 15 minutes. You want to watch the pizza and take it out when the top is light golden brownish and the crust is a light golden brown.

IMPORTANT: Let this pizza cool for 20 minutes, if you do not it will be all over the place. Once it cools for 20 minutes it will be just the right temp and will come out of the pan the right way. Cut and serve. This recipe should feed a family of 7 with maybe a slice left over.

Looking at it, I had that "aha!" moment. I've always wondered how they got the texture of the pizza crust - now I know. 1 cup of cornmeal. So, made it this weekend, with a few substitutions

* I couldn't find 18" deep-dish pizza pans nearby. So I bought 2 3"x13" pizza(?) pans at Ace-mart (local restaurant supply shop). A little too deep, but close enough!
* Halved the recipe. Which means that you get pretty close on the size (18" = 254 square inches, 13"*2 = 264 square inches). Which explains why I was a little short on crust. But also made it cheaper - I think I spent $30 on cheese alone.

Thoughts from making it:
* Garlic - rather than run in through a press, you can huck it in your blender first. Put on chop - when I dropped the garlic in (through the small opening on the top of the blender), it bounced around for a while, which wound up with it getting chopped into bitty bits quite nicely. (And we went with much less - 2 toes of garlic instead of 10). Faster than chop and it just falls straight to the bottom. Chop allowed it to keep getting flung about the blender.
* If you use the pan I did, don't attempt to go all the way up the sides. About half-way will do you. The slices still weigh 10 ounces or so, and definitely are deep dish pizza goodness.
* Maybe get whole tomatoes in a can, or just buy crushed. I bought diced, and about 3 seconds in the blender was all it took - both to mix it all up, and to make a horrid mess as my blender barely held all the tomatoes.

Some changes for next time, I think:
* Try to make a little more crust. Or just make sure I distribute it evenly. I wound up with ultra-thick sides, and not enough on the bottom. Still yummy, though.
* A little less of the parmesan/asiago/romano mix
* A little less cheese, a little more sauce. It seems like a ton of sauce - it's not.

Overall? A triumph. I'm making a note here: huge success.
Awesome Chicago pizza.

-TBD