Table Partitioning

Working on learning partitioned tables. We've done it, and it works really well for us, but it's time to start getting clever.

Was reading this, which is an excellent primer:
http://www.simple-talk.com/sql/database-administration/partitioned-tables-in-sql-server-2005/

A few rules I learned the hard way. I deliberately did it from scratch in hopes that I'd remember it better - what to do, what not to do. In particular, working on switching tables into partitions (our current stuff switches out, not in)

* clustered key of the table you're switching in has to be on the same filegroup as the partitioned table.
* indexes have to match. ALL of them.
* indexes are NOT on the partition scheme
* only one constraint (below)
* Add a constraint to make sure the partitioned key is within the partition range (order_date >='20100428' and order_date <'20100429' and order_date is not null)
* Then, SPLIT the range (code below), and SWITCH the table in.

    ALTER PARTITION SCHEME ps_daily NEXT USED your_partitioned_fg
    
    ALTER PARTITION FUNCTION pf_daily() SPLIT RANGE ( @Day) --@day is one day larger than current max date

    ALTER TABLE orders_stg SWITCH TO dbo.orders PARTITION 200

Now, what if you need to add an old day in? You have up to 5/4/2010, but need 5/3?

ALTER PARTITION SCHEME [ps_daily] NEXT USED your_partitioned_fg
ALTER PARTITION FUNCTION [pf_daily] () SPLIT RANGE (N'2010-05-03')

[Offtopic] How to print to a networked Laserwriter (XP)

Needed this desperately, so posting it here. Worked like a charm for me. Thanks to this Anthony Duplessis chap, whomever he is. Thanks!

Note that once you get to the test page print the first time, you're NOT DONE, so uncheck it. Follow the directions, step by step.

(found some screenshots at: http://blog.eppesconsulting.com/2009/05/05/AppleLaserWriter16600PSOnWindowsVista.aspx)

Printing to an Apple Laserwriter 16/600 using IP from a Win 2K and Win XP
client.

The following procedure explains how to configure desktop clients,
running Windows 2000 or Windows XP, without installing the appletalk
protocol.

• Select Start/Settings/Printers.
  
• Double-click 'Add Printer' icon. Click the 'Next' button.
  
• Select 'Local printer' bullet.
  
• Uncheck 'Automatically detect and install my Plug and Play printer' box.
  
• Click the 'Next' button.
  
• Select 'Create a new port' bullet.
  
• Select 'Standard TCP/IP Port' from the drop-down arrow list.
  
• Click the 'Next' button. Click the 'Next' button again.
  
• Enter the appropriate IP address of the printer. Click the 'Next' button.
  
• Select 'Apple Network Printer' from the drop-down arrow list in the 'device
  type' field.
  
• Click the 'Next' button. Click the 'Finish' button.
  
• Select the appropriate printer driver. Click the 'Next' button.
  
• Accept or modifiy the suggested printer name. Click the 'Next' button.
  
• Select the 'Yes' bullet if you want to make this printer the default, or
  
• Select the 'No' bullet if you do not want to make this printer the default.
• Click the 'Next' button.
  
• Select the 'Do not share this printer' bullet. Click the 'Next' button.
  
• Select the 'No' bullet so that a test page is not printed.
  
• Click the 'Finish' button.
  
• Open the Printers folder.
  
• Select and Right-click the apple printer. Select 'properties' from the
  context menu.
  
• Select the ‘Ports’ tab and then the ‘Configure Ports’ button.
  
• Verify that the 'LPR' bullet is selected in the Protocol section.
  
• Enter ‘lp’ in the 'Queue Name' field. [Note: that's LP, lowercase, not IP]
  
• Click to select the 'LPR Byte Count Enabled' box.
  
• Click the 'OK' button.
  
• Select the ‘General’ tab and then Click the 'Print Test Page' button.
  

Anthony Duplessis.
LIFE is the toughest teacher of all.
First, you get the test.
Then, you're taught the lesson.

[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)