Posts in category SQL Server

MDX: Scope Statement For All Measures in Multiple Measure Groups

Dec6th
2011 Leave a Comment Written by Will

This was irritating me today so I thought I would share. If you need to write a scope statement that will include all the measures in multiple measure groups; perhaps you’re implementing some Time Intelligence calculations and you want to have any new measures dynamically included, then the statement looks a little something like this:

SCOPE({MeasureGroupMeasures("Internet Sales"), MeasureGroupMeasures("Reseller Sales")});

I forgot the {} the first time around and was super confused by an error message when I attempted to deploy the MDX calculation to the cube that said that said “The END SCOPE statement does not match the opening SCOPE statement.” Because my SCOPE and END SCOPE statements looked just fine to me.

BI, Scripts Analysis Services, MDX, Scripts, SSAS

A New Goal for A New Life

Jan19th
2011 3 Comments Written by Will

There’s nothing like drastic, violent change that can make you reevaluate your life and future. Five months ago (just about to the day) my second daughter was born. That caused my wife and me to reevaluate our lives together and we decided that it made a lot of sense for her to leave her job and stay at home with the girls. We’ve been very happy with the decision and wouldn’t change it in the least, but it’s certainly been a learning experience for the both of us. Then last month I left my job to join a great consulting firm in the Twin Cities. I’m extremely excited about the change but consulting is a new world to me which only adds to the anxiety of starting a new job.

All of this change has caused me to look at my personal and professional goals and really take stock of where I’m going. The end result is that I’m pretty much tossing everything out the window and starting fresh. READ MORE »

goals, mvp

Partition Schemes Disabled by Default When Creating Indexes in SSMS 2008

Jul15th
2009 1 Comment Written by Will

We use partitioning quite heavily at my current job. And in order to take advantage of switching data from a partitioned table into a staging table all indexes must be aligned on the partition scheme. So, I was frustrated to find out that when you attempt to script out the create statement for an index in SQL Sever Managment Studio (SSMS) 2008 it does not include the clause to create the index on the partition scheme that the index was created on.

Thankfully, Microsoft wasn’t so dense as to remove this funtionality entirely, they simply turned it off by default (which I would argue is ridiculous, but they aren’t really going to listen to little old me). So, here’s the fix.

Open up Tools -> Options then change the setting at: SQL Server Object Explorer -> Scripting -> Script Partition Schemes to TRUE. (it’s at the very bottom of the list)

Indexes, Partitioning, SSMS

Table Disk Space Usage By Filegroup and Partition

Apr6th
2009 6 Comments Written by Will

I wanted a more granular level of detail when looking at disk space usage of our databases. I wanted to see the disk usage of each table. Since we use partitioning pretty heavily I also wanted to see how that usage was broken out by partition and filegroup. In our implementation we have a separate filegroup for each table partition but in a configuration that has multiple parititons on a partition this script can tell you usage for either metric.

Normally I’ll take this query and take the results over to excel and pivot the data so that I can filter and sum data in whatever way I want. I also have this running in a SQL Agent job and inserting into a table so that I connect to that table and do growth trend analysis.

Scripts DBA, Partitioning, Scripts, T-SQL

Database File Auto-Growth History

Apr5th
2009 Leave a Comment Written by Will

Here’s a quick script to find the recent history of file auto growth on a MSSQL system. I work with a large OLTP database that’s partitioned monthly. If we get files auto-growing toward the end of a month when the partition is already upwards of 60GB, timeouts start to crop up. Sometimes finding out where those timeouts are coming from aren’t readily apparent. This script helped us out when we were searching for recent file growth.

SET NOCOUNT ON
 
DECLARE  @path VARCHAR(MAX), @cmd VARCHAR(4000);
 
SELECT @path = CONVERT(VARCHAR(MAX),VALUE)
FROM fn_trace_getinfo(DEFAULT) WHERE property = 2
SET @cmd = 'dir /b "' + @path + '"'
-- PRINT @cmd
 
DECLARE @files TABLE
(
	fn VARCHAR(64)
) 
 
INSERT @files
EXEC master..xp_cmdshell @cmd
 
DELETE @files
WHERE fn IS NULL
 
SELECT
	e.DatabaseName
	, e.[FileName]
	, e.SPID
	, e.Duration
	, e.StartTime
	, e.EndTime
	, CASE e.EventClass
		WHEN 92 THEN 'Data'
		WHEN 93 THEN 'Log'
	END AS 'FileType'
	, f.fn AS 'TraceFile'
	, te.name
FROM @files f
	CROSS APPLY fn_trace_gettable(@path, DEFAULT) e
	JOIN sys.trace_events te
		ON e.EventClass = te.trace_event_id
WHERE
	e.EventClass IN (92,93)
ORDER BY
	e.StartTime DESC

Scripts DBA, Scripts, T-SQL

Posts in category SQL Server

MDX: Scope Statement For All Measures in Multiple Measure Groups

A New Goal for A New Life

Partition Schemes Disabled by Default When Creating Indexes in SSMS 2008

Table Disk Space Usage By Filegroup and Partition

Database File Auto-Growth History

Recent Posts

Categories

Archives