A couple days ago I was playing around with some Event Notifications and the Certificate that I’d created for them on my development machine.  Low and behold I’d made a classic mistake and forgotten to write down the password for the certificate.  Thus I was left without a critical component necessary for signing my procedures.  Since this really wouldn’t do, I opted to drop the certificate from the server.

Now this shouldn’t be such a problem a siimple DROP CERTIFICATE statement and I should be good to go.  The statement I used look just like this:

 IF EXISTS(SELECT * FROM sys.certificates WHERE name = ‘MyCertificate’)
    DROP CERTIFICATE [MyCertificate]
GO

Yup, Didn’t Work

There wouldn’t be much of a point to this if this worked as intended.  So here’s the rub, the statement ended up generating the following error:

Msg 15352, Level 16, State 1, Line 1
The certificate cannot be dropped because one or more entities are either signed or encrypted using it.

Backing up a bit, the reason that I created this certificate is that I’ve been using it to sign stored procedures.  I was doing that so that the procedure can execute under that a login that has the permissions I want rather that just opening a big security hole in my development machine.

What’s Signed By Your Certificate

To find out what’s signed by the certificate, you can use the sys.certificates and the sys.crypt_properties system views.  The query I use for this is below:

SELECT OBJECT_SCHEMA_NAME(co.major_id) + '.' + OBJECT_NAME(co.major_id)
FROM sys.certificates c
    INNER JOIN sys.crypt_properties co ON c.thumbprint = co.thumbprint
WHERE co.crypt_type_desc = 'SIGNATURE BY CERTIFICATE'
AND c.name = 'MyCertificate'

Hopefully, you won’t forget your certificate password like I did.  But you may need to remove a certificate from one of your systems some time and hopefully this get you passed that irritating error above.

Part of being in the technology industry requires a constant dedication to learning.  The world is a constantly changing place and the things you know today aren’t going to be cutting edge tomorrow.  Those that stay in place and don’t change with the times can sometimes appear to be zombies.  Just drooling along from one project to the next.

If you aren’t feeling the love of being a zombie, there is a great way to break out of this cycle.  That way is through learning.  But how do you know when or how much you’ve learned.  Well if you were a zombie, you’d count the brains you’d eaten.  But since you aren’t, you should get that infection looked at, certifications and exams are a good way to benchmark where you are in the learning curve.

Alas, though, taking those certifications and exams cost time and money.  Well, right now there is a way to remove the cost factor.

Free Brains

Gerry O’Brien announced last week on the Microsoft Certification blog that the Visual Studio 2010 Beta exams have been opened up. From April 4 through April 30 you can register to take any or all of the exams for Visual Studio 2010.  This is a great thing about going out and taking the exams now is that they are free.

The following exams are in beta and available to take for free:

• Exam 71-511, TS: Windows Applications Development with Microsoft .NET Framework 4
• Exam 71-515, TS: Web Applications Development with Microsoft .NET Framework 4
• Exam 71-513: TS: Windows Communication Foundation Development with Microsoft .NET Framework 4
• Exam 71-516: TS: Accessing Data with Microsoft .NET Framework 4
• Exam 71-518: Pro: Designing and Developing Windows Applications Using Microsoft .NET Framework 4
• Exam 71-519: Pro: Designing and Developing Web Applications Using Microsoft .NET Framework 4

I’d encourage everyone that can find the time to pop in and take an exam or six.  With the low cost remember I said free these are worth the attempt.  You may not pass, but you will get a chance to see the exam.  If you’ve never taken a Microsoft exam before, it’s a good way to see how they are and drop some of the fear of the unknown.  And who knows, maybe you’ll pass out of the gate.  You’ll never know unless you try and taking the beta exams is a great opportunity to comment and influence how the exam will look when it goes live.

These Count

Thought I should mention that the beta exams are as valid as the final exams.  So this is counts towards TS and Pro certifications.  Think of it, a little time and the opportunity to get a free certification.

Last week I presented at the March Minnesota SQL Server User Group (PASSMN) meeting on using CTEs in SQL Server.  The session is meant as an overview and introduction to CTEs with some examples of their use and what some of the effects can be.

Overall the session was well received and I got the following scores:

I had some pretty good comments from the attendees:

• Demos can be better organized
• Good job
• Great display of CTE uses
• Great stuff – I can’t wait to start putting them to use
• I found it to be a very interesting demonstration. My work still uses SQL 2000, but they’re looking to conver to 2008 later this year, so good.
• Very informative

And some that a little less than happy but still very useful for the next time I do this presentation:

• Seemed like too much time was spent on the surface repeating examples when we could have gone further in depth.
• Add some useful examples for takeaway
• Too much focus on simple example and not enough focus on complex useful examples
• Lots to cover, a bit rushed due to time constraints

For those that want to dig into some of the examples provided, here is the presentation for download.

Every now and then I work with people that just use the default trace templates available within SQL Server Profiler.   Using the default traces and you end up being limited to just some performance stats on procedures and batches that have completed.  There isn’t exactly an issue with doing this, except that there is so much more that can be done with Profiler.

For instance have you ever needed to do any of the following?

• Monitor for table/index scans or deadlocks
• Verify that applications were not under reporting errors
• Troubleshoot memory problem
• Check to see if there are any transactions utilizaing deprecated syntax

But how would you find out about doing any of this type of stuff.  The place I’d recommend starting is with Mastering SQL Server Profiler by Brad M McGehee (twitter | blog).  This book was originally writtern for SQL Server 2005, but it is still very much applicable.  And why am I bringing this up today?  Well, it was applicable enough for me to send off to a few people this week in response to their questions.

The SSWUG Spring 2010 Virtual Conference is coming up.  On April 7, 8, and 9, attend a conference from the comforts of your living room, office, or couch.  And while you are at it you’ll get to sit on on some incredible presentations, interaction with speakers, chat with other attendees, make new contacts, lots of fun and surprises.

• More than 80 technical sessions
• These are NOT sales presentations.
• Live keynote presentations and interaction
• On-Demand access to sessions for 45 days – miss a session? No problem.
• Chat, Twitter Integration, SKYPE integration for Q&A
• Experience-based learning – find out what you need to know from people that are using the technology every day
• 6-Month SSWUG.ORG Membership (or membership extension), included!
• ALL-ACCESS Pass: SQL Server and Business Intelligence… SharePoint and .NET technologies – all included, one low price
• Great vendor hall – learn about the best tools, technologies, publications and partners out there for your shop
• Learning, learning and more learning

If this sounds as awesome as it is, register for the event here.  Among the presenters will be myself with the following topics:

Using XML to Query Execution Plans

SQL Server stores its execution plans as XML in dynamic management views. The execution plans are a gold mine of information. From the whether or not the execution plan will rely on parallelism to what columns are requiring a key lookup after a non-clustered index seek. Through a the use of XML this information can be available at your finger tips to help determine the value and impact of an index and guide you in improving the performance of your SQL Server databases.

Prerequisites

• Understanding of basic T-SQL coding
• Understanding of basic XQuery statements

Goals:

1. Discuss information available in an execution plan
2. Demonstrate use of XQuery to query execution cache
3. Demonstrate methods to performance tune

Getting To Know Your Indexes

Without proper indexing SQL Server can be hard pressed to create efficient and performant execution plans. Dynamic Management Views (DMV) and system views provide a slew of information about indexes that can be used to analyze indexes within SQL Server. In this session we’ll go under the hood of SQL Server to look at DMVs and system views to know what indexes you have, should have, and how they feel about the way applications are treating them.

Prerequisites

• Understanding of performance tuning needs
• Experience with Database design

Goals:

1. Identify methods to analyze current and potential indexes
2. Learn how to alleviate stress found on indexes
3. Demonstrate methods for tuning indexes

A Function By Any Other Name Is A Function, Right? Right?!?

User defined functions provide a means to encapsulate business logic in the database tier. Often the purpose of the encapsulation is to provide standard method access segments of data within the database. Unfortunately, not all methods of creating user defined functions are equal. In this session we’ll review the types of user defined functions and investigate the performance impact in selecting the different types

Prerequisites

• Understanding of basic T-SQL

Goals:

1. Identify purposes for creating user defined functions
2. Discuss the types of user-defined functions
3. Demonstrate performance impact in selecting different types of functions

Stop in an check it out, there will be a lot of great sessions and great conversation.

After setting up backups on a client’s servers, I started to get the error message below each night at approximately the same time.  Sometimes on the same database and other times on other database.  The only constant appeared to be the time in which the error occurred.

Processed 1 pages for database ‘MyDatabase’, file ‘MyDatabase_log’ on file 1. [SQLSTATE 01000] Msg 16943, Sev 16, State 4, Line 116 : Could not complete cursor operation because the table schema changed after the cursor was declared. [SQLSTATE 42000] Msg 3014, Sev 16, State 1, Line 82 : BACKUP DATABASE successfully processed 329 pages in 1.141 seconds (2.362 MB/sec). [SQLSTATE 01000]

The error message is complaining that the schema for a table that is being used by a CURSOR has changed.  At 1 AM, this seemed to be a very bizarre event to be occurring.

It turns out that the error message actually had nothing to do with the backup commands, instead it was due to the CURSOR that was being used in the backup script to loop through the databases.  The backup script uses a progress table to monitor the backups as they are occurring and this table is used to populate the CURSOR.

Around the time the backup script failed, the index maintenance script had also started executing.  It just so happened that the index maintenance script was re-indexing the table that the CURSOR was based on.  A little re-coding around the use of the CURSOR resolved this issue.  And plans to remove the CURSOR entirely are forthcoming.

If you see error messages similar to this in your environment, try looking for CURSOR that are having their underlying tables defragmented.  This could very well be your issue.

It’s come down to a decision… do you want to learn about SQL Server and network with your peers or don’t you.  Then again, the question could also be do you want some swag and pizza with some work buddies.  Hopefully, you’ll be stopping in for the first item.

Out meeting is today starting at 2:30 PM for networking with announcements an presentations beginning at 3:00 PM.

This month we have Louis Davidson joining us for:

Database Design Fundamentals

In this session I will give an overview of how to design a database, including the common normal forms and why they should matter to you if you are creating or modifying SQL Server databases. Data should be easy to work with in SQL Server if the database has been organized as close as possible to the standards of normalization that have proven for many years. Many common T-SQL programming “difficulties” are the result of struggling against the way data should be structured and can be avoided by applying the basic normalization techniques and are obvious things that you find yourself struggling with time and again (i.e. using the SUBSTRING function in a WHERE clause meaning you can’t use an index efficiently).

And before that, I’ll be presenting:

ABCs of CTEs

Common Table Expressions (CTE) aren’t as common as their name implies. CTEs are often seen as a secret part of the dark art of recursion. This session will explore CTEs to show how they can be extremely useful in improving performance and legibility of T-SQL code. And, of course, we will look at their use in returning recursive data.

Hope to see you there.  If you have any questions, you can reach me on twitter.

A while ago I had read about the hidden threat of forwarded records.  These silent killers are like storm troopers in the bush waiting to get you when you least expect it.

And then the other day I saw a T-SQL Tuesday post by Sankar Reddy on (blog – twitter) on determining if your SQL Server is affected by forward records.  His post details what forwarded records are and how to repro and determine if they exist on a table.

Knowing about forwarded, I’ve monitored performance counters on these at clients throughout my engagements.  The performance counters can tell me if there is any performance impact being measured regarding forwarded records.  But what they can’t tell is what tables are causing these issues to occur.

So to that end, the following script is something I cooked up a while back to allow myself to check all of the heaps in a database and determine the count of forwarded records on those tables.  It uses a CURSOR, but that’s only because sys.dm_db_index_physical_stats doesn’t allow APPLY joins to it.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO

IF OBJECT_ID('tempdb..#HeapList') IS NOT NULL
    DROP TABLE #HeapList

CREATE TABLE #HeapList
    (
    object_name sysname
    ,page_count int
    ,avg_page_space_used_in_percent float
    ,record_count int
    ,forwarded_record_count int
    )

DECLARE HEAP_CURS CURSOR FOR
    SELECT object_id
    FROM sys.indexes i
    WHERE index_id = 0

DECLARE @IndexID int

OPEN HEAP_CURS
FETCH NEXT FROM HEAP_CURS INTO @IndexID

WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO #HeapList
    SELECT object_name(object_id) as ObjectName
        ,page_count
        ,avg_page_space_used_in_percent
        ,record_count
        ,forwarded_record_count
    FROM sys.dm_db_index_physical_stats (db_id(), @IndexID,  0, null,  'DETAILED'); 

    FETCH NEXT FROM HEAP_CURS INTO @IndexID
END

CLOSE HEAP_CURS
DEALLOCATE HEAP_CURS

SELECT *
FROM #HeapList
WHERE forwarded_record_count > 0
ORDER BY 1

Sometimes you have to be this geeky.  Here’s a Pi Pie for Pi Day…

If you aren’t hungry… here’s another slice of Pi

3.141592653589793238462643383279502884197169399375105820974944592307816406286208998628034825342117067982148086513282306647093844609550582231725359408128481117450284102701938521105559644622948954930381964428810975665933446128475648233786783165271201909145648566923460348610454326648213393607260249141273724587006606315588174881520920962829254091715364367892590360011330530548820466521384146951941511609433057270365759591953092186117381932611793105118548074462379962749567351885752724891227938183011949129833673362440656643086021394946395224737190702179860943702770539217176293176752384674818467669405132000568127145263560827785771342757789609173637178721468440901224953430146549585371050792279689258923542019956112129021960864034418159813629774771309960518707211349999998372978049951059731732816096318595024459455346908302642522308253344685035261931188171010003137838752886587533208381420617177669147303598253490428755468731159562863882353787593751957781857780532171226806613001927876611195909216420198938095257201065485863278865936153381827968230301952035301852968995773622599413891249721775283479131515574857242454150695950829533116861727855889075098381754637464939319255060400927701671139009848824012858361603563707660104710181942955596198946767837449448255379774726847104047534646208046684259069491293313677028989152104752162056966024058038150193511253382430035587640247496473263914199272604269922796782354781636009341721641219924586315030286182974555706749838505494588586926995690927210797509302955321165344987202755960236480665499119881834797753566369807426542527862551818417574672890977772793800081647060016145249192173217214772350141441973568548161361157352552133475741849468438523323907394143334547762416862518983569485562099219222184272550254256887671790494601653466804988627232791786085784383827967976681454100953883786360950680064225125205117392

Also, don’t forget about Daylight Savings if you are one of the those afflicted by this change in the time zone.

Ever need to group dates and round them to intervals?  I had this exact need for a report on wait stats I was working on for an upcoming post.  I wanted the flexibility to be able to group a list of dates by either minute, hour, date, etc.  And then also round those dates within those intervals.  For example, round a list of dates to every 15 minutes, every 3 hours, or every 2 days.  At first thought this sounded like something that might be a little tricky to accomplish.  But as I’ll show in the examples below, this is quite easy.

Start With The First of the Month

To resolve this issue, I started by using the logic that I typically when I need to take a date and change it to the first day of a month or quarter.  The logic I use for that utilizes the DATEADD and DATEDIFF functions.  I’ll skip going into how those work since Books Online covers that pretty well. 

To find the start of a month or quarter, these functions can be used together to be compare a date and time against the value 0.  Casting 0 as datetime returns the value 1900-01-01 00:00:00.000.  Using the two functions together, use DATEDIFF to determine how many months or quarters have occurred since 1900-01-01.  And then use DATEADD to add those months or quarters to the date 1900-01-01.  Doing this will provide you with the the first day in either the month or the quarter.

The examples below can be used to demonstrate this:

SELECT DATEADD(M, DATEDIFF(M, 0, '20100310'), 0)
GO

SELECT DATEADD(Q, DATEDIFF(Q, 0, '20100310'), 0)
GO

Returning the following results:

Round to the Desired Interval

Now that’s the easy part which leaves the next part, where dates and times need to be rounded not to the 1st of the month put to periods that are determined at execution time.  As I mentioned above, I need to change the logic above such that I can get dates to round to every 1 week or 2 days or 3 hours.

On the surface, this sounded a lot harder than it turned out to be.  Because if you use the % to find the remainder of division, you can easy round any date or time to any logical grouping of dates or times. 

Suppose you need to round a datetime to the nearest 4 hours.  Find the distance in hours between 1900-01-01 and the datetime in question.  Take that number and find the modulo using your rounding interval.  In this case that value is 4.  Then subtract the remainder from the distance.  And use the new distance value in the DATEADD function from 1900-01-01.  Hopefully, that didn’t lose too many people.

Rather than explain it another way, take a look at the following T-SQL statements where the datetime is rounded down to the nearest 2, 3, and 4 hour intervals.

--Round down to nearest 2 hours.
SELECT DATEADD(HH, DATEDIFF(HH, 0, '2010-03-10 03:00:00.000')-(DATEDIFF(HH, 0, '2010-03-10 03:00:00.000')%2), 0)
GO

--Round down to nearest 3 hours.
SELECT DATEADD(HH, DATEDIFF(HH, 0, '2010-03-10 03:00:00.000')-(DATEDIFF(HH, 0, '2010-03-10 03:00:00.000')%3), 0)
GO

--Round down to nearest 4 hours.
SELECT DATEADD(HH, DATEDIFF(HH, 0, '2010-03-10 03:00:00.000')-(DATEDIFF(HH, 0, '2010-03-10 03:00:00.000')%4), 0)
GO

These statements produce the following results:

As you’ll hopefully see, this is a very simple technique.  Imagine replacing the hour, day, or month date parts with a variable and then the interval to round the dates with a variable.  Doing that and the expression can be used in a stored procedure or report to group any set of dates along any set of date or time groupings.

Hope you find this as useful as I did.  And in the next couple days, I hope to have something up that uses this technique to make it even easier to use in your own code by providing fully working examples.