Recently I’ve been looking into new ways to deal with extremely large datasets. With Twitter and Digg making announcements recently that they’re migrating, or have migrated their operations to Cassandra, I’ve started to take a keen interest in NoSQL. NoSQL is a term that casts a wide net. In general it encompasses data stores that eschew the traditional relational database model in favor of distributed data stores. Google, Amazon, Facebook, LinkedIn and a host of others have all thrown their hats in the distributed data ring. I mention those companies specifically because all of them have built their own distributed database offerings to satisfy their particular needs. Google has BigTable, a propriety technology that has an open source flavor under the Apache Software Foundation project Hadoop called HBase. Amazon has Dynamo (even more proprietary). Facebook created Cassandra and open sourced it thankfully. And LinkedIn developed Voldemort (a great name if I do say so). Twitter and more recently Digg.com have gotten behind Cassandra which has given that particular project (not even up to version 1.0 yet) a lot of press lately. I’ve started to dabble with Cassandra and HBase (really the whole Hadoop project) in an attempt to get familiar with technology but I keep coming back to something in my mind. That is that “NoSQL” is probably the stupidest possible name that could have been coined to describe all of these amazing technologies.
Read more…
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)
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.
Read more…
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