[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

[Replication] Alternative to SQLMonitor (Replication Monitor)

SQL Server's Replication Monitor is great except for a few major issues
1) It has to be running all the time, and it consumes a large number of window resources.
2) Due to the way SQL reports issues in replication, it can show a publication as good, even if it's been erroring for 2 days.
3) It's active, not passive - I have to go look at it. It can't warn me via email or whatnot.

So, half a day digging through code and error messages, and I've come up with this. This is not fullproof by any stretch - I don't deal with Merge Replication, for instance. But in MY environment, it seems to work pretty well.

I'll see about adding a couple more fields, but I want to get this on paper.
(And yes, this is a request: if there's a better way than this, let me know)

--Courtesy of The Baking DBA
--Replication query that shows what's currently in error.
--1.01 added "delivering" exclusion, email, fixed 
--   line-too-long-breaks-blogspot formatting
IF OBJECT_ID('tempdb.dbo.##replication_errors') IS NOT NULL 
    DROP TABLE ##replication_errors

SELECT 
 errors.agent_id, 
 errors.last_time, 
 agentinfo.name, 
 agentinfo.publication, 
 agentinfo.subscriber_db,
 error_messages.comments AS ERROR
INTO ##replication_errors
FROM 
--find our errors; note that a runstatus 3 
--can be the last message, even if it's actually idle and good
(SELECT agent_id, MAX(TIME) AS last_time 
 FROM distribution.dbo.MSdistribution_history with (nolock)
 WHERE runstatus IN (3,5,6) 
 AND comments NOT LIKE '%were delivered.' 
 AND comments NOT LIKE ' GROUP BY agent_id) errors
INNER JOIN 
(SELECT agent_id, MAX(TIME) AS last_time 
 FROM distribution.dbo.MSdistribution_history with (nolock)
 WHERE runstatus IN (1,2,4) 
 OR comments LIKE '%were delivered.'
 GROUP BY agent_id) clean
 ON errors.agent_id = clean.agent_id 
 AND errors.last_TIME > clean.last_time
--grab the agent information
INNER JOIN distribution.dbo.MSdistribution_agents agentinfo
ON agentinfo.id = errors.agent_id
--and the actual message we'd see in the monitor
inner JOIN distribution.dbo.MSdistribution_history error_messages
ON error_messages.agent_id = errors.agent_id 
AND error_messages.time = errors.last_time
AND comments NOT LIKE '%TCP Provider%'
AND comments NOT LIKE '%Delivering replicated transactions%'

IF (SELECT COUNT(*) FROM ##replication_errors) > 0 
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'email_profile',
    @recipients = 'blah@tbd.com',
    @subject = 'Replication errors'
   ,@query = 'select * from ##replication_errors'
   ,@query_result_header = 0

DROP TABLE ##replication_errors