|

SQL Server and Other Topics

rss

I got my start with SQL Server in 1997 with SQL Server 6.5 at BellSouth Cellular. From that point on I've had the opportunity to work on Versions 6.0 to 2014. I specialize in Performance Tuning, High Availability and SQL Development. Contact me through this site or through my Twitter handle @SQLDiver


My current client has a monitoring "application" written by someone for SQL Server 2000. It has been added to for following version, 

but much of the code is old and written for SQL Server 2000 (i.e. not utilizing newer functionality and features).

One such function of monitoring the "application" is doing is gathering the ERRORLOG data from the SQL Server error log files every 5 minutes using sp_readerrorlog stored procedure. For those not in the know this stored procedure returns every record in the ERRORLOG file without the ability to provide a data range. It simply calls xp_readerrorlog which does have parameters for passing a date range to only retrieve records within that specific date range.

The current process builds a execution string which is then executed using osql which is a deprecated tool. The process loops through a table containing server names that have been added for monitoring then calls a stored procedure, passing the server name in as a parameter.

The current process is calling sp_readerrorlog every 5 minutes (every record), parsing the entire data set then filtering out the existing records before inserting into a log table in the database. Because he was using osql to execute, the parsing of the data was quite complex and unwieldy.

Instead of osql, I opted to write a stored procedure to select through a linked server to the servers remote servers to be monitored. I won't cover the creation of linked servers in this article, but it could be simply automated to check for the existence of a linked server with the name of the server you want to monitor and create it if it doesn't exist.

A few things before getting started: you will want to make sure your security is correct for your linked servers (meaning you probably want to make sure your SPNs are configured correctly: Register a Service Principal Name for Kerberos Connections. And you might have issues with MSDTC if not correctly configured: Troubleshooting Problems with MSDTC.

So let's get into the meat of the process. A table called sqlErrorLog exists with the following schema:

 
   
SET ANSI_NULLS ON 
GO 
 
SET QUOTED_IDENTIFIER ON 
GO 
 
SET ANSI_PADDING ON 
GO 
 
CREATE TABLE [dbo].[SQLErrorLog]( 
   [SQLErrorLogID] [int] IDENTITY(1,1) NOT NULL, 
   [LogDate] [datetime] NOT NULL, 
   [ServerName] [varchar](255) NOT NULL, 
   [ProcessInfo] [varchar](100) NULL, 
   [Text] [varchar](8000) NULL, 
 CONSTRAINT [PK_SQLErrorLog] PRIMARY KEY CLUSTERED  
( 
   [LogDate] ASC, 
   [ServerName] ASC, 
   [SQLErrorLogID] ASC 
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] 
) ON [PRIMARY] 
 
GO 
 
SET ANSI_PADDING OFF 
GO
   
 

 

Frankly I hate the design, no foreign key, bad column names. I recommend creating a primary key on ymd, serverName and ukey in that order. I didn't have that luxury as this was a third party application. If you would like to change the column names 

 

 
   
-- SELECT TOP 10000 * from sqlErrorLog el ORDER BY LogDate desc 
-- exec dbo.PopulateErrorLogByServerName 'LOCAL' 
ALTER PROCEDURE dbo.PopulateErrorLogByServerName ( 
   @servername varchar(100) 
   ) 
   
AS 
   
SET NOCOUNT ON; 
BEGIN 
   --IF OBJECT_ID(N'tempdb..#ErrorLogs') IS NOT NULL 
   --   DROP TABLE #ErrorLogs 
   
   --IF OBJECT_ID(N'tempdb..#ErrorLog') IS NOT NULL 
   --   DROP TABLE #ErrorLog 
   
   -- Create Temp Tables used for processing errorlog records 
   CREATE TABLE #ErrorLogs ([Archive #] smallint, [Date] datetime, [Log File Size (Byte] int) 
   CREATE TABLE #ErrorLog (ID int IDENTITY(1,1), LogDate datetime, ProcessInfo varchar(25), [Text] varchar(max), [ServerName] varchar(256)) 
   
   -- Variable declaration 
   DECLARE @ROWS int 
   DECLARE @curDate datetime = getdate()    
   DECLARE @date nvarchar(20) = convert(varchar(20),@curDate,20) 
   DECLARE @datestart nvarchar(20)  
   DECLARE @maxdate datetime 
   DECLARE @FileNum tinyint 
   DECLARE @ExecString nvarchar(1000) 
   DECLARE @ParameterDefinition nvarchar(2000) = N'@FileNum tinyint, @datestart nvarchar(20), @date nvarchar(20)' 
   PRINT @ExecString 
  
  
    SET @ExecString = N'[' + @servername + '].[master].[sys].[xp_enumerrorlogs]' 
 
   -- Get list of errorlogs 
   INSERT INTO #ErrorLogs 
   EXECUTE sp_executesql @ExecString 
     
   SELECT @maxdate = max(LogDate) 
   FROM dbo.sqlErrorLog sel 
   WHERE serverName = @servername 
 
   IF @maxdate IS NULL 
   SELECT @maxdate = min(Date) FROM #ErrorLogs sel 
      
   SELECT @datestart = convert(varchar(20),@maxdate,20) -- format for execution 
 
  
   SELECT TOP 1 @FileNum = [Archive #] 
   FROM #ErrorLogs  
   WHERE Date <= @maxdate 
   ORDER BY Date desc 
 
   -- Loop through files if errorlog has been cycled (or missed) 
   WHILE @FileNum Is NOT NULL 
   BEGIN 
   
   set @datestart = @maxdate 
   
   SET @ExecString = N'[' + @servername + '].[master].[sys].[xp_readerrorlog] @FileNum,1,null,null, @datestart,@date,''asc''' 
 
   INSERT INTO #ErrorLog (LogDate, ProcessInfo, [Text]) 
   EXECUTE sp_executesql @ExecString, @ParameterDefinition, @FileNum, @datestart, @date  
   
   UPDATE el 
   SET [ServerName] = @servername 
   FROM #ErrorLog el 
   WHERE [ServerName] IS NULL 
   
   SELECT @FileNum = MAX([Archive #]) 
   FROM #ErrorLogs  
   WHERE [Archive #] < @FileNum 
   
   END 
   
   -- Populate sqlErrorLog with records that do not exist 
   INSERT INTO sqlErrorLog ( LogDate  , ProcessInfo  ,Text,ServerName) 
   SELECT el.LogDate, el.ProcessInfo, LEFT(el.Text,8000), el.ServerName 
   FROM #ErrorLog el  
   LEFT JOIN dbo.sqlErrorLog sel 
   ON el.LogDate = sel.LogDate 
   AND el.ServerName = sel.ServerName 
   AND el.ProcessInfo = sel.ProcessInfo 
   AND el.Text = sel.Text 
   WHERE sel.LogDate IS NULL 
 
   SET @ROWS = @@ROWCOUNT 
 
   IF @ROWS > 0 
   PRINT CAST(@ROWS as varchar(8)) + ' Records Added' 
   ELSE 
   PRINT '0 Records Added' 
   
END 
  
 


Sessions

It is always a challenge to attend half of the sessions I set out to attend. I walked in double and triple booked for many of the key time periods. Knowing this I pre-purchased the recordings. 

Favorite Sessions

Dealing with Multipurpose Procs and PSP the RIGHT Way! by Kimberly Tripp 

Kimberly's sessions are always interesting and I always leave with something. This session was no different. She went into detail about strategies she recommends for the dreaded "all purpose" , one for all procedures and how to "defeat" the parameter sniffing issues encountered with these types of procedures. 

She demonstrated the OPTION(RECOMPILE), dynamic SQL and a very interesting pattern for getting the best performance. If you missed it, watch her blog, no doubt she'll cover it completely.

Having Paul Randal in the front row was an added benefit, adding the fun occasional banter between them. 

BI Power Hour

The BI Power Hour is always over the top hilarious. Matt Masson, Matthew Roche and others profess to present a worthless uninformative session about BI (lies) and always show the ease and functionality of the Power BI tools through humorous demonstrations. You have to be early to the room in order to get a seat, as the line forms outside the door and around the corner. The BI Power Hour is a must see!

Lightning Talks

6 speakers, each has ten minutes to present a topic. I got to witness the Lightning Talks 103 session with Randy Knight, Julie Smith, Stuart Ainsworth, Rick Heiges, Argenis Fernandez and Kevin Boles. I'm lucky enough to call 4 of the six friends, so it was extra fun for me. 

The audience was fully engaged, the presenters were hilarious and took every opportunity to tease each other just to keep the ball rolling. If you ever have the opportunity to see any of these 6 present, do it. 

Query Tuning Mastery: Manhandling Parallelism

All I can say about this one is wow. Adam showed some very interesting ways to "trick" the optimizer to improve the performance of a query through parallelism. This one appeared on PASStv, and is a must see. It truly warps reality! I can't wait to play with his demos to try to fully understand what he is doing. 

 

Keynotes

Dr. Rimma Nehme - It is quite unfair to be put into the position of stepping into the shoes of Dr. David DeWitt. Everyone wondered how it could be possible for anyone to come in and keep the audience mesmerized as Dr. DeWitt always did. The session started out a little rough with the clicker not working, but she tap danced past it once she got it working.

Fact is, Rimma did it by winning over the audience with an excellent presentation filled with humor. She presented Cloud services using a Pizza as a Service example. 

For a great breakdown of the talk see Erin Stellato's blog here.

Networking

Monday - Steve Jones and Andy Warren's Networking event. Buffalo Wild Wings let down Steve and Andy by totally turning their back on the community by turning everyone away?! Weird, strange..great... we found a new place, The Yard House, huge place, opened their arms to over 100 people. Great food and a great time. I met quite a few new people at the event and hung with some old friends. Overall a success...can't wait until next year.

Tuesday - The Welcome Reception is a great place to meet people, and this year was no less...then SQL Karaoke with Denny Cherry and SIOS. Always the top networking event at PASS, Denny chose a great venue, Cow Girls, for SQL Karaoke. The bartenders were awesome, waitresses hustled and always there when you needed them.  It was definitely a who's who of SQL Server. More MVPs per square inch than the MVP Summit (small bar, lots of people). I met at least 10 new people and spent more time with friends I get to rarely see. Big Score! 

Wednesday - Too many events not enough time, the Exhibitor Reception for munchies, meeting sponsors and more networking ...then on to Pragmatic Works Karaoke. Too much fun watching drunk people singing. Great time...

Thursday - Kilt Thursday was the first time I wore a kilt. Wearing a kilt is showing support for Women in Technology. Kilts of PASS Summit 2014

SQL Wine with some close friends and some new friends. Gareth brought out his secret stash of Biltong, and we sampled the wines each person brought to sample. Next year will be bigger and better.

The evening went on until the morning, I missed the PASS Appreciation Event completely, winding up the morning walking back to the hotel with a group of friends at 4AM in the morning after a hearty breakfast of scrambled eggs, bacon and toast. Best Summit ever!

Atlanta Kilts

 


 

Friday - After the sessions, exhaustion has set in. By buddies, Rob Volk and Jason Carter were in heated competition with 2 other speakers in the Speaker Idol contest. All of the speakers were awesome, Rob and Jason placed 2nd and 3rd respectively (and respectably). I felt they should have been 1st and second. Planned to meet the gang at the Tap House, but the wife and I hit the Yard House for dinner, then crashed. 

Saturday - After meeting Rob Volk for breakfast at Lowell's, and seeing other #SQLFamily members JRJ and Adam Jorgenson, it was time to say goodbye and enjoy our weekend. Karen, my wife, and I did a Lake Union and Lake Washington tour. Too cool, we saw Bill Gate's house and the Sleepless in Seattle floating home. 

Sunday - Breakfast at Lowell's again and a nice walk around the Pike Place Market. On our flight back to Atlanta I sat behind Stuart Ainsworth and his wife. Great trip home and great to be home. 

PASS Summit is an event that should never be missed by a data professional.


Marketing

Starting months ahead of time, the email marketing was in process. The marketing was communicating to the speakers what was going on, and reaching out to the potential attendees. The email marketing talked about the raffle prizes, Guidebook, Twitter, SpeedPASS, the calendar of events for the Orlando PASS and MagicPASS user groups. 

It was very informative, not overbearing and not rapid fire which leads to the attendees and speakers to stop reading them.

I know I'm not even coming close to all of the marketing efforts used to entice the attendees to come out for the event, but here is the best place to go to find out more details from Andy Warren from his blog that has up to 48 different parts for Building the SQLSaturday Orlando Marketing Plan–Part 1. If you are a SQLSaturday organizer you must read Andy's blog. Thanks Andy!

Event

The amount of effort that goes into an event the size of the Orlando SQLSaturday is super human. The teams start meeting many months ahead of the event to start planning for every facet of the event to make sure registration goes smoothly, the speakers make it to their sessions and can get setup in time, ensure the sponsors are where they need to be an are happy, attendees are able to easily find where they need to be for the sessions they want to attend, plenty of caffeine and water (boy was it hot), so there aren't speakers and attendees dropping like flies from dehydration or lack of caffeine. The organizer's kids were doing a great job making sure we had what we needed when we were parched. 

Who better to learn from than the originators of SQLSaturday? We had Andy Warren, Karla Landrum, Rodney Landrum, Kendal Van Dyke, Shawn McGehee, even Tammy Clark who came down from Nashville to help out  (keeping things flowing). I know there are many more that I've missed, please forgive me as every single job was immensely important to the success of the event. The volunteers were everywhere running around doing the odd jobs to make sure nothing got missed. 

Lunch

SQL Saturday 318 Chef CoatOne of the coolest things about the event is the speakers served lunch to the attendees. The SQLSaturday organizers purchased chef coats for the speakers serving lunch, and we all fed excellent BBQ brisket and chicken to the attendees. It was really fun to talk to the attendees as we served them.

Speakers

With speakers at the level of Bradley Ball, Louis Davidson, Argenis Fernandez, Allen White, Andy Warren, Janis Griffin, Randy Knight, Jack Corbett, Geoff Hiten,  Rob Volk, Ed Watson, David Klee...and many many more...you just can't see them all. I spent many hours with my fellow speakers talking about their sessions and what they were doing. I've built a close friendship with many of these speakers and love pulling knowledge from them. I even got to attend a few sessions after mine was completed. 

After Party

We're all exhausted, the organizers are beyond exhausted but it is an opportunity to talk with the attendees and fellow speakers. Attendees have the chance to drain a little more knowledge from the speakers, share a beer and dinner. BTW, the food was awesome especially the egg rolls and wings. 

Summary

All in all, we drove 488 miles to spend time with friends old and new and we can't wait until next year. The event was well organized, the lunch was phenomenal, the attendees, speakers and sponsors all seemed to be very satisfied (and sufficiently exhausted).

We walked away with more ideas for our own SQLSaturday in Atlanta for next year. 

Thanks MagicPASS, OrlandoPASS, the organizers and volunteers! Hopefully we'll see you all in Atlanta next year. 


PINS, PINS, PINS

I'm very behind in blogging about our SQL Saturday event held in May. I wanted to share some tips about our vendors, this one is about pins.

Two years in a row we've created a pin for our SQL Saturday.  This has been one of the most cherished giveaways I've seen. 

I was lucky enough to have the opportunity to design the pin from SQL Saturday 285. The organizing team decided that it was impossible to let the use of I-285, Atlanta's perimeter interstate bypass, go by. 

The pins were purchased from a company LapelPins4Less which helps to take your design and make it "pin-able". 

They're design guys were very quick in converting your graphic into a graphic that more closely resembles the final product.

The proof for the 285 pin (right) very closely matches the actual pin on the left. 

The sales people were very easy to work with, and handle quick changes easily. We were able to ask for a range in quantity in order to make an educated decision about how many based upon estimated attendees and sponsor money.

We ordered the pins and had them in less than a month. 

I highly recommend LapelPins4Less if you decide to create a pin for your SQL Saturday event!

Don't hesitate to let me know if you have questions or would like my help with design ideas! I can't wait to see what next year's pin will be.


I don't remember where I got the base for this query, its been very helpful for me to run across databases and servers to get a list of missing indexes to examine. 

Key Point: Do not just add the indexes identified as missing. Look at the indexes, verify they are not duplicate indexes, TEST the indexes. Many of the "MISSING" indexes won't improve the performance of the query. Some indexes may improve the query performance a little, but will add additional index maintenance overhead for updates and deletes. You must always weight the cost vs. benefit.


SELECT
mid.statement,
migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,
OBJECT_NAME(mid.Object_id) as TableName,
mid.equality_columns, mid.inequality_columns, mid.included_columns,
'CREATE INDEX [IX_' + OBJECT_NAME(mid.Object_id) + COALESCE('_' + PARSENAME(mid.equality_columns,1),'')
+ COALESCE('_' + PARSENAME(mid.inequality_columns,1),'')
+ CASE WHEN mid.included_columns IS NOT NULL THEN '_wInc' ELSE '' END + ']'
+ ' ON ' + mid.statement
+ ' (' + ISNULL (mid.equality_columns,'')
+ CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END
+ ISNULL (mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,
migs.*
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10
AND mid.database_id = DB_ID()
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC
OPTION(RECOMPILE)
  
  


You can find thousands of examples of how to use an OUTPUT clause in your T-SQL, but few show how to output specific columns, and let say you want to loop through the record set limited to the top 10 rows at a time (you'd probably limit to 10k or 100k, but for example) here you go:

The reason I may do this would be to limit the impact of locking in a production database for a table where you want to delete the rows and record the deletes.

 

DELETE TOP(10) tb

OUTPUT deleted.TableID, deleted.Field1, deleted.Field2, deleted.Field3, deleted.Field4

INTO [archive].[Table] (TableID, Field1, Field2, Field3, Field4)

FROM [dbo].[Table] tb

 


Have you ever been memory constrained do to physical hardware constraints (or budget constraints)? Well Microsoft has decided to help us out a little bit. The addition of the Buffer Pool Extension, adds the ability to extend buffer pool to nonvolatile random access memory for instance an SSD. 

This functionality allows the buffer pool manager to use the NAND memory from the Flash Storage to maintain a pool of lukewarm pages (Microsoft term not mine). In effect the buffer pool manager is using the Flash Storage as Level 2 buffer pool that only writes clean pages for safety purposes. 

Although the buffer pool extension can be up to 32 times the size of RAM, the recommendation is to maintain a ratio between 1:4 and 1:8. As usual, the optimal ratio can vary, so test as is always the recommendation.

Refer to the following technet article for more information.


Lets start with, "where do I go to find help?" 

I have my favorites I've followed for years, the go to guys that have always be very informative and even more important accurate. I like to start all of my consulting engagements for performance tuning at the Server and disk configuration. If you don't have a solid foundation you won't have a solid architecture. You won't be able to scale as the product grows. For instance, in my current engagement, they don't know if they have a scalability issue or not (they do) , so the work is finding all of the potential issues.

Disk IO is critical to the SQL Server performance. It can be a major contributor to performance problems and everyone talks about it. My favorite bloggers that blog about storage include Wes Brown and Denny Cherry. Both blogs have a wide variety of details about many different topics. 

Performance Tuning is one of my favorite topics. If you don't love working on a process that takes minutes to complete and tuning it to sub-second then you're not living. My favorite performance tuning bloggers is pretty huge (and includes Wes and Denny): Grant Fritchey, Paul Randal, Kimberly Tripp (and SQLSkills team), Brent Ozar (and team),  and much more. 

How about server, windows, and/or database configuration? Here some more (besides the guys above): Linchi Shea, Joe Chang, and many more. 

This is a lot of information, and  a good place to start. Don't trust just anyone, there are some really bad recommendations out there that will get you into trouble. Stick with the guys that come up with an idea, test it, discuss it and admit when they got it wrong. 

The next part will talk about certifications...do we need them? What is their value? How do I start?

 


On May 3, 2014, the always popular, highly attended SQLSaturday #285 will be hosted! Atlanta is known as being an awesome venue with many sessions at all levels of experience. 

Watch Twitter hash tag @SQLSat285!

Link to registration page and information.

Register Now!

Add to your calendar!