Sad SQL Joke

I read this joke today
A SQL query goes into a bar, walks up to two tables and says, "Can I join you".

Oh man...

xSQL Profiler Take 2

Today I decided to use xSQL Profiler to help me diagnose some memory pressure/paged out problems we are facing, and boy, that was probably an error (partially my fault to trace too much perhaps)

Problems:

• I was tracing a remote server A (trace files are located on A's E drive) starting 12:30 pm
• xSQL Profiler repository is located on my computer J, that go fetch trace files from server A
• I think my computer cannot catch up to the trace files creation, and in ten minutes I have 6GB of trace files on server A and running low on space.
• I killed the xSQL Profiler hoping it would stop (at this time about 80,000 rows collected)

And....

• It did not stop!! and drove my local SQL Server memory to 1GB and slowed my laptop to a crawl
• I could not start the "xSQL Profiler" as it says it cannot connect to the database despite being there
• I could not start the "xSQL Profiler Service" as it creates 10+ traces insantly on server A again
• I tried deleting entries in the local "xSQLProfiler" database tables (such as dbo.Traces, dbo.APPLICATION_SETTINGS, dbo.TRACE_SCHEDULES....) => as soon as I started xSQL Profiler, it created the 10+ traces AGAIN.
  

Fix:

• Created the SQL script below to KILL ALL traces except the default one (I ran about 10 times this afternoon)
• Renamed the trace file directory on server A's E drive [this made it stop, finally, near 3:30 pm]
• I still cannot open up the xSQL Profiler application (cannot connect to database)
• EXTREME CAUTION for NEXT TIME
  

This is the code I ended up with, and added to my toolbox
It kills ALL Profilers trace but the default SQL trace


DECLARE @traceid INT
DECLARE curDBs CURSOR FAST_FORWARD FOR -- fast forward = read only, forward only
   --Run the following Transact-SQL statement to retrieve the list of the running trace:
   SELECT DISTINCT traceid
   --,*
   FROM ::fn_trace_getinfo(NULL)   
   WHERE traceid <> 1 -- do not kill the default SQL trace

OPEN curDBs
FETCH NEXT FROM curDBs INTO @traceid
WHILE @@FETCH_STATUS = 0
BEGIN

   --Run the following Transact-SQL statement to stop the server-side trace
   EXEC sp_trace_setstatus @traceid = @traceid , @status = 0
  
   --Run the following Transact-SQL statement to close the trace and to delete the trace information
   EXEC sp_trace_setstatus @traceid = @traceid , @status = 2

   FETCH NEXT FROM curDBs INTO @traceid
END

CLOSE curDBs
DEALLOCATE curDBs

SELECT * FROM ::fn_trace_getinfo(NULL)

RETURN


Daylight Saving Time and SQL Server Job

We encountered an issue with Daylight Savings Time in our hourly transaction log after the Nov 2, 2008 Daylight Saving Time change.

Problem:
The transaction log is set to occur every hour, then copied over to standby servers for automatic restore

There was a transaction log for Nov 2, 2008 1:00 AM EDT (e.g. 200811020100.trn)

Next transaction log should be Nov 2, 2008 2:00 AM EDT (200811020200.trn), but since DST ended by one hour back at 2 am, 2 am EDT is now effectively 1 am EST
The OS time is now Nov 2, 2008 1:00 AM EST, and guess what, the transaction log file name is again 200811020100.trn, effectively overwrote the original 1 am log

How We Fixed It?
We ended up applying Differential backups since the last Full backup to bring the databases to the newest state, and then apply all the transaction logs after to catch up

How To Prevent It?
We change these servers to use UTC timestamp (namely GMT Reykjavik timezone in Windows), and increase current SQL Jobs by 5 hours ahead to run in the "correct" low traffic hours. Now we do not need to worry about the gap between 1 am & 3 am for Spring, and 1 am & 1 am for Fall.

Notes:
This problem ONLY occurs for regular-interval jobs, not for specific-time driven jobs
And supposedly the OS will never reach 2 am but 1:59:59 am EST, and then 1:00:00 am EDT so that the job will not run twice (and that SQL will adjust any job which ran between 1 and 2 am EDT for you)

Thoughts:
In short, it's shocking it's still happening, and I know why IT people love to use UTC now despite it's confusing for East Coast employees. Microsoft's How to prepare SQL Server 2005 and SQL Server 2000 for changes to daylight saving time in 2007 article.

New SQL Skills I have learned from jobs - Part 3

This is a continuation of New SQL Skills I have learned from jobs - Part 1, Part 2

 

SQL Server 2008 Spatial Index

• For the first time, native Spatial functions inside SQL Server (not 3rd party extensions) and it's a good start
• Speed is quite fast with proper usage of Spatial Index (dividing earth into quadrants and quad-trees)
• You can check for intersection, container and many other relationship between locations (either a Point, or a Polygon)

SQL Server 2008 Full-Text Search

• An improved version of the Full-Text search engine since 2000, merged into the Database Engine (instead of separate process). New features include Stoplists and Word Breakers
• Still one big black box that's hard to fully understand or customize for one's specific needs. Ability to custom-rank will be awesome if it exists. For now, it is just Google-alike text search, literally. If you want If X rank highest; If Y rank higher, etc... good luck!

SQL Server 2008 Integration Service

• I enjoyed the built-in Fuzzy Search and Fuzzy Grouping, makes finding duplicate data much easier (say looking for duplicate address based on Street Name, City, State, Zip, Phone #, etc...)
• Powerful if run on a powerful machine
• I am used to the Drag-and-Drop GUI interface for everything now - SSIS, Maintenance Plan, rarely would I need to write custom code or T-SQL scripts to extend the current abilities 

REVIEW: xSQL Profiler

I came across the xSQL software website and realized they have a few Free SQL Server products, so being the curious monkey I am, I downloaded them and installed. Here's my first usage of xSQL Profiler and a short review

What I liked:

• Easy to use and setup a quick trace
• Filter (in both setting up or querying) and interfaces are better than SQL Profiler trace
• Easy to define Category/Event Definition
• Lets you pick a repository database
• Custom Query Template is useful to find Top N queries by CPU time/Duration/Execution etc...
• Able to schedule the trace (one-time/daily/weekly/month)
• e.g. I can setup a Daily trace from 10am to 11am to get a snapshot of busy times

What could improve:

• Let me think on it... maybe more licenses for the Free version (or for me?)
• Uses up a lot of memory when Viewing Trace Data (e.g. to view 81000 rows, my local SQL Server memory incrased by ~200MB, and so did the xSQL.Profiler.UI.exe and took about 2~3 minutes to display

So far so good, it shall remain my first choice for Profiling for now, unless I am in Servers where it's not installed. Keep up the good work, xSQL! This is something Red Gate didn't have =P

New SQL Skills I have learned from jobs - Part 2

This is a continuation of New SQL Skills I have learned from jobs - Part 1

High Availability Features - Mirroring and Log Shipping

• Mirroring is a easy-to-setup high availability feature, the SSMS GUI makes it easy to setup as well
• The Restore and manual Failover is the difficult part, especially for the front end application
• Log Shipping is even better than Database Mirroring as it provides means to have a warm standby SQL Server with couple clicks and a network share

XML/XQuery

• I was right to fear the powerfulness of the XML language
• In its defense, XML provides means to store free-form data inside the database (i.e. you can consider XML a database inside a database column)
• However, performances greatly suffer compared to RDBMS structures and manipulation is not as straightforward (finding syntax alone was a challenge - .modify, .value, .query, .exist, .node etc...)