SQL RNNR

Another month has crept up upon us.  It is time once again for the Las Vegas User Group /PASS Chapter to meet.  This month we will have Stacia Misner (Blog | Twitter) present to us: “PowerPivot – Why Should You Care?”

The meeting place is the usual location at the Learning Center

Learning Center of Las Vegas
777 N Rainbow, Ste 250.

We are also making this group meeting available via Livemeeting once again.
All guests can attend at:
https://www.livemeeting.com/cc/UserGroups/join?id=C7N873&role=attend

Meeting ID is C7N873 (just in case you need it).

We hope to see you virtually or in person.

Also, S3OLV is now on twitter.  Follow @S3OLV

Well, I have taken another plunge.  I finally got around to submitting a session for SQL Saturday Salt Lake City 2010 (Web | #sqlsat54 ).  I had been planning on doing this since it was announced through the grapevine.  The delay for submitting the first session really came down to a bit of confidence in writing an abstract on the topic.  Writing abstracts is new territory for me.  I plan on improving in this domain, thus will need to submit a few more abstracts to various different events.  That said – SQL Sat SLC will be the first but not the last.  If you check out the website for SQL Saturday 54, you will see that my topic is one of those that brings agony to many DBA’s – Documentation.  As I said in the abstract, I hope to share some scripts to help simplify some of the documentation tasks.

I am looking forward to this event and hope to see some of you there.  Here’s to hoping my presentation will be selected.  In the meantime, I will be working on another abstract for a second topic to present at SQLSat54.

We are here again after another week and ready for another episode in this series.  Today we get to talk about chapter 6 in the book by Alex Kuznetsov (Blog).  You can find more on this series by looking here.

Common Problems with Data Integrity

Alex explains to us that SQL server has built-in tools to help maintain the integrity of the data.  He quickly lists out three areas that can be useful in maintaining Data Integrity, as well.

1. Applications
2. Constraints
3. Triggers

Application

Data integrity can be enforced from the application.  It should be noted that this could be an inefficient method of ensuring the integrity of the data.  If you try to do it from the application, be prepared for a little pain.

Constraints

It is well known that using constraints is the most reliable way to enforce data integrity rules.

Using constraints in the database is helpful and quick.  If data does not meet the constraint criteria, we can prevent it from being added.  There are different kinds of constraints and one should become familiar with the various constraints.  In this section, Alex also demonstrates the differences for the three states of constraints.  It is important to understand the state of the constraint to ensure the integrity of the data is at the level your expect.

There is a section devoted to UDFs in this chapter as well.  The section is within the Constraints section and should be read.

Triggers

The chapter is concluded with the discussion on using Triggers for enforcing data integrity.  Triggers are suitable in some areas where a constraint would not be suitable for enforcing data integrity.  Triggers do have their own problems that should be considered too though.  Some of the problems are with how the trigger was coded and some are inherent with the use of triggers.  It is well worth learning about these limitations by reading this section as well.  Test the samples provided and implement some of the suggestions in your triggers.

Conclusion

Alex concludes this chapter with the following statement:

Data integrity logic in the application layer is too easily bypassed. The only completely

robust way to ensure data integrity is to use a trusted constraint. Even then, we need to

test our constraints, and make sure that they handle nullable columns properly.

We also have a nice set of reminders of what to include in our assumptions to be tested.  The short of it, check your constraints and test as many scenarios as necessary based on your assumptions.

Not a sound from the pavement

Have you ever come across a SQL query that used to run faster?  Has that query just recently begun to run considerably slower?

I just ran into one of those situations.  I had a query that should have been running in seven minutes or less (string parsing nearly 4 million records).  When it started running much slower, I decided to pull out some monitoring scripts and start checking a few things on the server.  I wanted to check for the usual suspects first and then find what was causing the alleged problems.

If you touch me you’ll understand what happiness is

When problems arise in a database, it is good to have a toolbelt of scripts available to help in quickly “touching” / assessing the problem.  In this case, I first turned to the following:

Code block

SELECT percent_complete, estimated_completion_time, cpu_time, total_elapsed_time, READS,writes,logical_reads
	,session_id,scheduler_id,start_time,status
	FROM sys.dm_exec_requests
go

My goal from this query is to quickly assess if the query is still progressing.  For the query in question, I should see that the query is steadily writing to the database.  I can also see really quick the status of the query.  Running this in conjunction with sp_who2 is helpful in identifying, at a high level, issues that may be occurring.  Another possible query to run in this kind of situation would be sp_whoisactive by Adam Machanic (Blog | Twitter), or the who2 script I wrote about here.  The idea, is to get a quick glimpse at this point to see if there is a potential issue.

In this case, I found nothing alarming or noteworthy from the initial queries.  Other queries could be run at this point to try and determine if there is a pressure point somewhere on your resources (memory, cpu).  I decided to take a quick glance at task manager just to get a quick visual of the server.  Looking at it, I saw that I had no memory available – all 96 GB were in use.  I proceeded to check the max memory setting on SQL server.  As expected, it was still set at the default setting.  A little further prodding showed that the SQL server process was consuming most of the memory available to the server.

I changed the max memory to 64GB and immediately saw improvements.  In task manager the effect could also be seen, as shown to in the pic to the left.  NO server restart or service restart was required.  This change took effect immediately.  The poorly performing query was soon performing back to normal.

I can smile at the old days

This should serve as a reminder to check your server settings from time to time and ensure that they are appropriately set.  This little change (and that was the only change), allowed my query to return to normal processing which was > 5 times faster than the problem range.  Happy troubleshooting.