[Powershell] What a piece of $#!+

Wow. So, Powershell is Microsoft's idea of UNIX: a command-line shell. Unfortunately, more than a bit half-assed, even with version 2.0.

I grabbed a module to allow UI scripting. Here's what I have to do in order to run a simple Hello World with it:

1. download Powershell
   
2. download PowerShellPack
   
3. install both
   
4. start Powershell
   
5. Set-ExecutionPolicy Unrestricted (so I can run scripts)
   
6. Import-Module PowerShellPack (needs to be done each time)
   
7. Hit ctrl+c because it's going to ask me once per script, and there are about 50. And, each time I run powershell.
   
8. Set-ExecutionPolicy Bypass (hello, usefulness, goodbye protection. Grrrr)
   
9. Import-Module PowerShellPack
   
10. Import-Module WPK
    
11. New-Label "Hello, World" -Show
    
12. get odd error: Exception calling ".ctor" with "0" argument(s): "The calling thread must be STA, because many UI components require this."
    
13. read blog
    
14. change shortcut to Powershell to add -STA
    
15. restart Powershell
    
16. Import-Module PowerShellPack
    
17. Import-Module WPK
    
18. New-Label "Hello, World" -Show
    

Success!

WTF. Am I a bad person if I wonder why the hell I have to jump through all these hoops?

[Partitioned Tables] Does the clustered index take up space if referenced?

Setting up a new partitioned table with associated indexes. I was curious whether the adding the partitioning key to any index would cause the index to grow - I expected not, but you never know.

Our clustered index:

create unique clustered index clustind_pk on ourtable (id, partitionedkey)

Our test indexes:

create nonclustered index A on ourtable (partitionedkey, fielda)
on ps_daily (partitionedkey)

create nonclustered index B on ourtable (fielda)
on ps_daily (partitionedkey)

create nonclustered index C on ourtable (fielda) include (partitionedkey)
on ps_daily (partitionedkey)

Then used this query to check a particular partition for all 3 indexes (thanks to Simon Sabin). Each was within 1 page of the others.

select OBJECT_NAME(p.object_id ), i.name,p.*
from sys.dm_db_partition_stats p
join sys.indexes i on i.object_id = p.object_id and i.index_id = p.index_id
WHERE p.object_id = 2071234567
AND partition_number = 28 

[Install] Restarting your SQL service within SQL

DOES NOT WORK. Not deleting the post below in case someone comes across this. We wound up using powershell instead. WMI-process.



We're building a Powershell script to automatically build out our machines. Not just the install, but the full config: backup jobs, maintenance, tempdb files, model, etc, etc.

So, we need to bounce the service so that all the tempdb files get created, the agent knows the database config, etc.

The trick isn't stopping the server: "net stop mssqlserver" will do that. The real trick is starting it back up, once you've shut down the SQL server. Since our solution is all done via SQLCMD we needed a way, within SQL itself, to start back up.

Our secret is the ampersand; when the command line interpreter catches it, it views it as you hitting the "Enter" key. So, even though the SQL service is off, the job continues.

declare @sql varchar(8000)
select @sql = 'net stop SQLSERVERAGENT & ping -n 15 127.0.0.1 & '
+ 'net stop MSSQLSERVER & ping -n 15 127.0.0.1 & '
+ 'net start MSSQLSERVER & ping -n 15 127.0.0.1 & '
+ 'net start SQLSERVERAGENT'
EXEC xp_cmdshell (@sql)

[Replication] Finding the commands that are breaking

Here's a simple one I had to do today. Documenting since I hate having to dig through the help files.

On your distributor:

SELECT * FROM distribution.dbo.MSpublisher_databases

That gives you a list of the source databases from the publishers. You want the ID field.

Now,

use distribution
go
sp_browsereplcmds @publisher_database_id = 13

will list all the waiting commands. The command field is the actual command, while the article_id can be gotten from running the following on the publisher:

sp_helparticle @publication = 'your_publication_name'

[Sp3] Another reason not to use DDL Triggers

So, I've already posted about my issues using DDL triggers with Replication (http://thebakingdba.blogspot.com/2009/12/replication-and-ddl-triggers-do-not-mix.html). Well, it looks like it may have contributed to issues with an active/active cluster upgrade (2005 SP3).

Installing the patch on the first active node, we got a failure on the upgrade. The error message? "Target string size is too small to represent the XML instance". But, oddly enough, the passive (which is upgraded first) worked and was on SP3.

So we deleted the trigger (which, interestingly enough, showed that replication uses DDL triggers - which makes sense but I hadn't thought about), and ran it again.

It failed again, but this time because the passive node wasn't upgraded. Check both - and yes, SP3. Roll back and forth, everything looks good. A sucky upgrade, but we got through it.

[Tuning] SPARSE varchar calculation

Since it doesn't appear that anybody has done this before, here you go. I've been comparing SPARSE to COMPRESSION, and for my particular tables, I got 25% space savings via parse. However, I got 40% savings from ROW compression, and 50% savings from PAGE.

Standard
=(Number_Of_Rows*(Average_Varchar_Length+2)
*((100-Percent_Null)/100))
+(Number_Of_Rows*(Percent_Null/100*2))

Sparse:
=(Number_Of_Rows*(Average_Varchar_Length+4))*((100-Percent_Null)/100)

Next up is comparing the CPU for each option. Nobody's really talked about whether the compression is symmetric or asymmetric, though I'd hope it's asymmetric (aka easier to decompress than compress, in this case)

[Jobs] Quick & Dirty - is the job running?

Needed a simple piece of code to tell me if a job was running or not. Several ways to do it, but this is the simplest and probably dirtiest.

EXECUTE sp_get_composite_job_info @job_id = 'B74856BF-3326-4B9F-B3A6-B1D182E1F300', @execution_status = 1

IF @@rowcount = 1
PRINT 'job running'
else print 'nope, not'

sp_MSforeachDB - skipping databases

SP_MSforeachDB is awesome - an easy way to walk through every database and run some code.

But what about databases you don't _want_ to touch?

sp_MSforeachdb 'if ''?'' NOT IN (''tempdb'',''model'',''a'',''b'',''c'') 
--sp_updatestats required after reorg, but not after rebuild
begin 
use ?
exec sp_updatestats 
end' 

Kulich - Russian Easter Bread

FINALLY, something baking related! It's been too long, I've been resting on my laurels.

I'll update this once I've made it myself. One other way to make it is inside a coffee can - I need to get details on that, since that's how I always had it - bullet-shaped, funnily enough, with a sprinkle-laden glaze on top.

6 cups sifted flour
3 egg yolks
1 whole egg
3/4 cups sugar
1 teaspoon salt
1 and a 1/2 packages yeast (come in a pack of 3)
1 and 1/4 cups WARM (not hot) milk
1 stick (1/4 pound) melted butter. 
 (Let cool off so it's not hot.)


Put yeast and milk in bowl and mix.
put eggs, sugar and salt in another bowl and mix on low speed.
Put milk and yeast together with the flour.
Add the eggs,sugar, salt mixture in.
Add melted butter.
Mix well with a wooden spoon.
Knead the dough mixture about 10 minutes (maybe more).
Dough needs to be soft.
Put a cover over the bowl so it can rise.
Dough needs to rise for an hour or more.
Needs to become twice its size.
Separate into 2 loaves.
Put 1 cup raisens for each loaf
Preheat oven to 350.
Use loaf pan sprayed with PAM or greased with butter.
Let dough rise again for 40-60 minutes.
If you want a shiny crust, use the whites from the 3 eggs.
Beat the whites for just a minute with a fork and brush the mixture
over the crust before you put in oven.
Bake 45-60 minutes.
Check dough with toothpick at 45 minutes to see if ready.
Pull the bread out and put on a towel.

[Code] IF EXISTS table check

Because I keep coming over here to find the snippet...

if object_id('tempdb..##temptablename') is not null
    drop table ##temptablename
CREATE TABLE ##temptablename (id INT IDENTITY, filelist VARCHAR(255))

or

 SELECT * FROM tablename.dbo.sysobjects 
 WHERE id = OBJECT_ID(N'tablename_goes_here') 
 AND OBJECTPROPERTY(id, N'IsUserTable') = 1