Today, Please see my featured article appearing on SQLServerCentral.com discussing SQL Agent Job Ownership.
Have you ever had a situation where a DBA or admin leaves the company, his login is disabled, only to discover that his years of service, and of creating sql jobs to run under his account, are now all suddenly failing? Backups, Maintenance Jobs, Packages, etc. 10, 20, 30, 40, 50 servers +.
What is the DBA left behind to do? That is the topic of this article and what you can do to automate and fix the issue accross your SQL Server infrastructure, and how to prevent it from happening in the future, using the scripts provided.
Please read on in Let's Talk Ownership (SQL Jobs).... http://www.sqlservercentral.com/articles/SQL+Jobs/68764/
Thanks for reading! Hope this is helpful - RP
DB Audit Challenge #1
And the Winner is….
First, I want to thank everyone that participated in the DB Audit Challenge, and appreciate your positive comments and feedback. One reader thanked me for running this challenge. Another was happy to stay engaged b/c he could not attend PASS. Many of you were preoccupied with or attending PASS, so I hope to provide more challenging challenges soon.
I’ve received some interesting replies, and some very subtle questions like, ‘how do I figure this out?’, and ‘it’s impossible to read the transaction log without one of those products’. Some more creative replies just dumped the whole thing into an online HEX editor, and just sort of did one of those word searches to find the answer.
But, one of the best, and winning answers, was definitely well-thought out and wholly complete. While I asked for the contents of at least in one of the field columns presented, I asked for the whole data row to be constructed from the hexadecimal output provided. This fine SQL DBA, not only gave us the correct answer, he broke it down exactly how it should be and explained how he figured it out. (See above reply, now that fellow will know how to figure it out :-)
Thanks for playing and hope to have the next challenge coming your way soon.
So, without further adieu, the Winner of the first DB Audit Challenge is: drum roll, please!
Michael Lato. Congratulations! Michael is a SQL Server Specialist at DataTamer Inc., hailing from Canada. He has more than 10 years of experience consulting on various database systems with a special focus in the CRM (Customer Relationship Management) field. Michael's past roles have been as diverse as system designer, project manager and head trainer. Michael is currently a member of the Quest SQL Server Expert Panel and he speaks regularly at SQL conferences such as PASS and SQLTeach. He is also open to new sql server opportunities, so if you can use his services, feel free to reach out to him at michael.lato@datatamer.ca
Michael ‘s comments, along with his answer on this contest were:
‘ Well, that was an interesting challenge. This was a deeper dive into the transaction log than I’ve ever done before and the hexadecimal conversions threw me for a little bit until I found the endian references. Here are my results and the codes I extracted to get them:
ItemNumber: 103109
ItemName: OctHoliday
CatalogSkew: SCARY10101
ItemDescription: Happy Halloween
EntryDate: 2009-10-31 22:11:39.963
NoAvailable: 1
Source: RowLog Contents 0
0x30003200C59201004F6374486F6C6964617953004300410
05200590031003000310030003100C5C06D01B39C000001000
0000600C00100570048006100700070007900200048006100
6C006C006F007700650065006E00
Source Breakdown:
0x - header
30 - status A
00 - status B
3200 - position of number of columns in data row (50) required byte swap
C5920100 - ItemNumber,int4 (103109) required byte swap
4F6374486F6C69646179 - ItemName,char10 (OctHoliday)
5300430041005200590031003000310030003100 - CatalogSkew,nchar20 (SCARY10101)
C5C06D01B39C0000 - EntryDate,datetime8 (2009-10-31 22:11:39.963) required byte swap
01000000 - NoAvailable, int4 (16) required byte swap
0600 - number of columns in data row (6) required byte swap
C0 - null bitmap ()
0100 - number of variable-length columns (1) required byte swap
5700 - offset, end of first variable-length column (87) required byte swap
480061007000700079002000480061006C006C006F007700650065006E00 - ItemDescription,nvarchar300 (Happy Halloween)’
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
For SQLCentric Monitoring and Remote DBA Services goto http://www.pearlknows.com
SQLCentric now supports SQL Server 2008!
Well it certainly has been a busy and exciting week at PASS. I couldn't go this year, but through the magic of SSC.com, the blogosphere and personal email from Seattle, I was there in spirit. You might say I was virtually there.
Speaking of virtual, one of the sessions I've followed was the Virtualization VC Breakfast Session, sponsored by VMWare, highlighting real-life experiences and challenges with SQL Server in virtualized environments. The debate to virtualize SQL or not continues.There's been a lot on this in the news and events this week, and I'll add my two-cents next week.
I even got pinged by one of my seasoned BI colleagues, Chuck Kelley manning one of the booths at PASS, hoping to hook up with Brian Knight. I left a message, but there's always a next time :-) So, let's hear some feedback on Brian's politically incorrect presentation.
My new buddies from India, Jacob Sebastian and Pinal Dave were there, as well Greg Larsen, who was given honorable mention in Andy Warren's blog as one of those to watch. And as if Steve Jones didn't have enough to think about on the way out the door to take off for Seattle, I pinged him about when my next article's going to be published. (Sorry man, I hope it wasn't my fault on the luggage mishap)
Ok, I dropped enough names, I feel like a gossip columnist. No offense to the ones I didn't. During this time, I made some new friends and connections in the SQL World. I hope to collaborate with them and bring some new and great contributions to the SQL community.
My blog has been appropriately light this week, making room for all the PASS MVPs, attendees and presenters, bringing us up-to-date coverage from Seattle. I didn't want to compete with all this, so I'll pick up the pace in the week to come.
So, in absence of technical content, and to keep those who didn't attend PASS in a good mood, I launched my first DB Audit challenge #1 (still one more day to play, before I announce the winner(s).) I will mention and profile those who participated.
Check out the challenge at http://www.sqlservercentral.com/blogs/pearlknows/archive/2009/11/01/db-audit-challenge-1.aspx I've received very positive feedback and thank you's on this, so I will do my best to bring a new challenge soon.
Finally, maybe I'll have an MVP Profile of the week again, but this week at PASS, all our MVPs and presenters there deserve to be named MVP of the week for their extraordinary, outstanding contributions and ongoing service to the SQL Server community!
Great job ALL! On behalf of the sql community at large, we thank you!
Hey, just caught wind of some timely stuff going on at PASS. Had my trusty 'Google alert' email me, set to ring on anything about the function fn_dblog.
Kalen Delaney, the sql internals extraordinaire, who brought us the book called Inside Microsoft SQL Server 2005: The Storage Engine, was in good form with her session on 'Care and Feeding of the Transaction Log' She went over the internals of the transaction log, and using the undocumented functions DBCC LogInfo, as well as the less resource intensive flexible fn_dblog function, to read and translate the transaction log.
In my own presentation on DB Forensics, I spoke at length on the Transaction Log, based on some of the materials in Ms. Delaney's book.
Here is one attendee who blogged about the t-log session, http://www.networkworld.com/community/node/47069
It was noted that one could talk about the transaction log for the whole day. Indeed!
So, those of you who attended the session at PASS, I am challenging you to my DB Audit Challenge and invite you to solve the t-log puzzle.
I posted some hints and clues on my earlier blog here: http://www.sqlservercentral.com/blogs/pearlknows/archive/2009/11/03/db-audit-challenge-1-part-ii-get-a-clue.aspx
Is there anyone out there that's up to the challenge? (Ms. Delaney and other MVP's excluded)
In the spirit of my Who DID IT and RAN SQL Caper presentation.....
OK, I was hoping to get the creative juices stirring, and now I will provide some clues and direction on how to solve the T-log puzzle. It's actually very simple (easy for me to say :-). One just needs to know HOW to read the transaction log. Come on, where's all the internals geeks out there?
First, to sweeten the pot, I will INDEED provide some prizes to be announced shortly - hey, I'm still waiting to hear back from the goody providers.
If you haven't yet seen the first entry on this, I invite you to take the DB Audit Challenge here:
http://www.sqlservercentral.com/blogs/pearlknows/archive/2009/11/01/db-audit-challenge-1.aspx
Above, you will find the directions on how to play (no scratch-offs), and a schema, as well as the hexadecimal t-log content is all there for you to decipher the 'hidden' message. So here are some clues, both technical and non-technical:
To read the Transaction Log output (see blog post above), you will need to find the position of the variable-length column called ItemDescription. Since it is an nvarchar, it will be stored in Big-Endian order. Find the position of the column, and convert it to an nvarchar - hey, I just told you how to do it - now you have to find it in there. The goal is to reconstruct the entire data row (all column data based on the schem provided). Since it is the ONLY variable length column in this schema, once you find the first position, the rest of the hex data will be the entire content needed to translate the data.
Oh, one final clue: It is a DELETED record, otherwise known as a 'Ghost Record'. I completed and published this challenge on October 31, 2009. Send all answers to sqlcentric@pearlknows.com - Good Luck!
Take the DB Audit Challenge - Intermediate - DB Audit Challenge #1
For those of you who have been following my recent blogs, as well as those who attended the SQLSaturday October 24 event in NJ, know that my presentation covered several topics with respect to database forensics. I demonstrated the various tools and methods, by leveraging the native out-of-the-box features in SQLServer, to audit and recover data. Topics included DDL Triggers, the Default Trace, CDC, and the Transaction Log. I gave a review of db internals on how to read the transaction log through the very useful system function: fn_dblog.
I present to the SQL Community at-large the first of perhaps a series of DB Audit Challenges below.
In order to decipher the puzzle, I am including the schema of a simple table called Item, with the column names, data types and length shown in Figure 1A. In addition, I am including the hexadecimal output of the RowContents0 field from the Transaction Log of a captured Delete Operation, otherwise known in the log as an LOP_Delete_Rows operation executed against the Item table in Figure 2A. Deletes are a logged operation and therefore can be recovered from the transaction log. These records are known as ‘Ghost Records’.
Using the information provided, I am asking you to reconstruct the data row, and give me the exact contents of the ItemDescription field. In this field is a simple message apropos for this time of year.
If you can reconstruct the contents of the entire row, I will consider it a bonus, but just want to see how many people can tell me what it is that’s in the ItemDescription column. I’ll run this challenge for the week, and will drop some clues and hints throughout to solve the puzzle.
Ok, if you need some help, you can use the book called Inside Microsoft SQL Server 2005: The Storage Engine – by Kalen Delaney. Also, if you were fortunate enough to catch my presentation, you received some handouts that can also help!
OK, so this is an internals exercise on translating the contents of the Transaction Log. If you are up to the challenge, then it’s time to get busy, and send your answers to sqlcentric@pearlknows.com.
I will post the results of how many folks got the right answer.
Figure 1A
Please note, I’ve formatted the output below into four rows for readable format. Take the four rows and concatenate it into one string, as you begin you journey to find the hidden message, and reconstruct the row. I will consider giving away some prizes, depending on how many people get the right answer. The first official hint is, remember, that all fixed columns appear first in the log, followed by variable-length columns.
I hope you find this interesting. Good Luck and stay tuned to this blog for more clues!
RowLog Contents 0
6C006C006F007700650065006E00 Figure 2A
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Coming soon! Our fully integrated SQL Server OS Live Performance Dashboard!
In the meanwhile for SQLCentric monitoring, and remote DBA services, check us out at
In the meanwhile for
http://www.pearlknows.com
Well, it's a wrap! Saturday has come and gone, but memories will go on. It was great to see so much effort and planning in a relatively short time to come together in one great event. I had a fantastic time. If you missed it, no worries, much discussion and materials surrounding the event have already been posted on Jacob Sebastian and Michael Cole’s blog. More photos and presentations will be posted shortly. We had an podcast expert on hand! It was truly an honor to present with two great MVP's Michael and Jacob! Special thanks to the SQLDiva herself, Melissa Demsak, leader and coordinator of the NJ SQL User Group.
Jacob makes mention of our trip to NJ. Took a little effort to find the place (Guess I should have made a right instead of left :-). What we didn't know as Michael's presentation was underway, is that his topic "Where in the World?" on spatial data and links to google maps was about us! We could have used this info then.
It was a pretty good crowd for a Saturday - close to 100 sql server dba's and developers. It was great meeting this nice bunch of folks! Excellent questions from many in the audience. Jacob, who came after me, gave some great speaking techniques. Like, if you don't know the answer, tell them we will take it off-line (LOL) - he should have told me that before! :-) But, bookended by such talented and bright MVP's, we did a good job of trying to get all questions answered. We even deferred to some smart audience folks who helped out as well.
Special thanks to our sponsors, Microsoft and Red-Gate, who provided much needed nutrition and some cool SWAG. We gave away great prizes, including autographed books written by Jacob and Michael. I was lucky enough to grab an extra copy of Jacob's book. (Hey Mike, you gotta get me your Pro T-SQL book) Prizes included, The Art of XSD – SQL Server XML Schema Collections, by Jacob, Michael’s book Pro T-SQL 2008 Programmer’s Guide, several copies of Windows 7, including a Signature Edition copy, T-shirts from Red-Gate and many more. Also, we gave away two 3-licensed copies of my core award-winning SQL Server monitoring and alert product - SQLCentric.
Michael's presentation was very interesting and unique as he brought us great insight into SQL Server 2008's new spacial data support, and geocoding. By far, the biggest and most exciting change for developers is the SQL Server 2008 support for spatial data—a powerful addition to the SQL Server programmer's toolbox.
I think it's unanomous that the surprise speaker of the day was in fact Jacob! We all knew he was a great author, but now we know it is matched by his presentation skills! He took a dry topic like 'Error handling' and turned it into one great interactive session that brought a teriffic conclusion to a terrific day! Those of you who are planning to attend PASS next month, make sure you sign up for Jacob's presentation, and not to miss out!
Thanks to all in attendance who gave up their Saturday to be with us! Feel free to ping me with questions and comments anytime!
I look forward to working with Jacob, Melissa and Michael again. New York's Microsoft HQ is under construction (which brought us over the Hudson to NJ), and should be completed just in time for spring - where, we'll do it again with exciting topics on all things SQL.
Now that it's behind us, I hope to get back to a more regular blog schedule :-)
Signing off - RP
UPDATE: I just finished up my presentation slides for this upcoming NY/NJ SQL User Group meeting, on Saturday, 10/24/2009. I may cut a few slides, but I'm certainly done adding any. Probably a few too many, but some of them are just section dividers, and I can always skip a subtopic if I'm short on time. Perhaps a few small modifications, but for the most part - I'm done. I also created my script files, and have them ready for some of the Demos I plan to do. I will be providing handouts for the audience members to follow along on some parts. Now for the dry run....
So, my presentation, "Who Did IT and Ran" - a SQL Caper, will be a focus on Database Forensics, of how to employ native sql server views and functions, to find out who did what to your database and when. It will give you ideas on how to get your SQL Server in compliance (think SOX), without the use of 3rd party solutions.
I will be discussing the various methods to retrieve this information, such as the Default Trace and DDL Triggers. We will explore the internals of the Transaction Log, and attempt to read it in its native HEX format. This will be sort of a magician's secret revealed - the algorithm how the popular 3rd party vendors do it in their compliance and data recovery software. Luckily, with the advent of SQL Server 2008's Change Data Capture, which will also be a discussion and demo topic, I will show you the far easier way to capture DML.
Finally, we will be giving away great prizes, SWAG, including FREE licenses for our SQLCentric monitoring & alert system!
I hear we still have some seating availability left, so to register and for more info on this FREE event and for FREE Breakfast and Lunch, see the link below. Hope to see you there!
The NJ and NYC SQL Server User Groups would like to invite you to a FREE Saturday Event featuring Jacob Sebastian, Michael Coles, and Robert PearlDate: Saturday, October 24th Time: 9:00am - 3:00pm (Sign-in begins at 8:15am) Location: Microsoft Office (Iselin, NJ) Registration: Required For Registration Link/Bios/Topic Summary/More Detail, please see: http://njsql.org/blogs/user_group_news/pages/NJSQL_2F00_NYCSQL-Saturday-Event-_2D00_-Saturday_2C00_-October-24th-_2800_9am_2D00_3pm_2900_.aspx User Group Websites: NJSQL - http://njsql.org/Default.aspx NJSQL Google Group: http://groups.google.com/group/nj-sql-server-user-group NYC - http://nycsqlusergroup.com/ Sponsors: Red-Gate, Moore-Stephens Business Solutions, LLC, Pearl Knowledge Solutions, Inc, Apress
Hi, all;
The countdown to the NY/NJ SQL Saturday event this upcoming Saturday has begun. And it's NOT TOO LATE to Register if you are or will be in the NY/NJ - TriState Area. Jacob Sebastion and Michael Coles will be joining me to bring you fantastic back-to-back presentations. Prizes, giveaways, SQL Knowledge, and FREE LUNCH, will all be given away at this FREE event. Please see all the details and links below on where to go and how to register.
Hope to see you there!!
- Robert Pearl
---------------------------------------------------------------------------------------------------------
NJSQL/NYCSQL Saturday Event on October, 24th (Registration Required) Here are the details, of my previously announced SQL Saturday event hosted by the NY/NJ SQL Server Professional Group in NJ's Microsoft HQ: This is my first SQL Saturday presentation event, and I am honored that I will be accompanied by SQL MVP's Jacob Sebastian and Michael Coles. Special thanks to the SQLDiva herself, Melissa Demsak, leader of the NJ SQL User Group! Seating is limited, so if you'll be in the New York, New Jersey area, REGISTER now! Look forward to seeing you there! See details below: The NJ and NYC SQL Server User Groups would like to invite you to a FREE Saturday Event featuring Jacob Sebastian (India). Date: Saturday, October 24th Time: 9:00am - 3:00pm (Sign-in begins at 8:15am) Location: Microsoft Office (Iselin, NJ) Registration: Required For Registration Link/Bios/Topic Summary/More Detail, please see: http://njsql.org/blogs/user_group_news/pages/NJSQL_2F00_NYCSQL-Saturday-Event-_2D00_-Saturday_2C00_-October-24th-_2800_9am_2D00_3pm_2900_.aspx User Group Websites: NJSQL - http://njsql.org/Default.aspx NJSQL Google Group: http://groups.google.com/group/nj-sql-server-user-group NYC - http://nycsqlusergroup.com/ Sponsors: Red-Gate, Moore-Stephens Business Solutions, LLC, Pearl Knowledge Solutions, Inc, Apress Swag: Books, SQLCentric License, Cool Stuff from Red-Gate Presentation #1 Speaker: Michael Coles (Clifton, NJ) Topic: "Where in the World?" -- Spatial Data in SQL Server 2008 Presentation #2 Speaker: Robert Pearl (New York, NY - Pearl Knowledge Solutions, Inc. - www.pearlknows.com) Topic: Who Did it and Ran? Presentation #3 Speaker: Jacob Sebastian ( India - http://beyondrelational.com/blogs/jacob/ ) Topic: Best practices for exception handling and defensive programming in SQL Server
As we continue to get ready for the NY\NJ SQLSaturday User Group meeting this October 24, 2009, I had the fortune to meet and plan this meeting with SQL Server MVP, developer and published author, Michael Coles. He is also the Principal ETL Architect at Moore-Stephens Business Solutions.
Michael has over 15 years experience designing and building SQL Server databases and SQL business solutions. A prolific writer on all aspects of SQL Server, particularly on the expert use of T-SQL, he has authored dozens of published articles and several books including "Pro T-SQL 2008 Programmer's Guide", "Expert SQL Server 2008 Encryption", and "Accelerated SQL Server 2008". These fine works are available on Amazon.com, by clicking the hyperlinks above.
Michael is currently working other book projects and many, many things, including his presentation for the SQL Saturday event.
He will be presenting on Spatial Data in SQL Server 2008, and is titled “Where in the World?” The topic summary is as follows: SQL Server 2008 introduces two new core data types for storage and manipulation of geospatial data. You can use SQL Server's built-in library of spatial operations to perform complex geographic calculations quickly and easily, and interface the spatial data types with front end mapping tools. The R2 release of SQL Server 2008 adds even more spatial data functionality to the Microsoft BI stack via Reporting Services improvements. In this session attendees will learn how to store, manipulate, and perform calculations with geospatial data in SQL Server to enrich their applications.
He also runs the NYC/North Jersey SQL Server Professionals on LinkedIN.
Finally, get ready to do some SQL squats. As a noncommissioned Army officer, Michael maintains his blog, on SQLBlog.com, appropriately called, Sergeant SQL. (Get down and give me twenty!) Here, as the headline says, you will get SQL Server development, news and information from the front lines.
You can reach out to Michael directly at admin@geocodenet.com.
I look forward to seeing him at the NJ SQL Saturday event, and appreciate his help in getting things together. He will be giving away some of his great books, so don’t miss out. And therefore, I am making Michael Coles, my MVP Profile of the Week!
------------------------------------------------------------------------------------------------
Next, I will talk about my presentation topic, and some of the things I’m working on for the event.
If you are still interested in registering for this FREE one-day event, please hurry, space is limited and closing fast! More details, including registration link at: http://njsql.org/Default.aspx
Here are the details, of my previously announced SQL Saturday event hosted by the NY/NJ SQL Server Professional Group in NJ's Microsoft HQ:
This is my first SQL Saturday presentation event, and I am honored that I will be accompanied by SQL MVP's Jacob Sebastian and Michael Coles.
Special thanks to the SQLDiva herself, Melissa Demsak, leader of the NJ SQL User Group!
Seating is limited, so if you'll be in the New York, New Jersey area, REGISTER now! Look forward to seeing you there! See details below:
The NJ and NYC SQL Server User Groups would like to invite you to a FREE Saturday Event featuring Jacob Sebastian (India). Date: Saturday, October 24th Time: 9:00am - 3:00pm (Sign-in begins at 8:15am) Location: Microsoft Office (Iselin, NJ) Registration: Required For Registration Link/Bios/Topic Summary/More Detail, please see: http://njsql.org/blogs/user_group_news/pages/NJSQL_2F00_NYCSQL-Saturday-Event-_2D00_-Saturday_2C00_-October-24th-_2800_9am_2D00_3pm_2900_.aspx User Group Websites: NJSQL - http://njsql.org/Default.aspx NJSQL Google Group: http://groups.google.com/group/nj-sql-server-user-group NYC - http://nycsqlusergroup.com/ Sponsors: Red-Gate, Moore-Stephens Business Solutions, LLC, Pearl Knowledge Solutions, Inc, Apress Swag: Books, SQLCentric License, Cool Stuff from Red-Gate Presentation #1 Speaker: Michael Coles (Clifton, NJ) Topic: "Where in the World?" -- Spatial Data in SQL Server 2008 Presentation #2 Speaker: Robert Pearl (New York, NY - Pearl Knowledge Solutions, Inc. - www.pearlknows.com) Topic: Who Did it and Ran? Presentation #3 Speaker: Jacob Sebastian ( India - http://beyondrelational.com/blogs/jacob/ ) Topic: Best practices for exception handling and defensive programming in SQL Server
Hi, ALL. Sorry I've been light on content this week. I've been battling a cold, and working on some excellent materials for the SQL Server Community at-large. I'll also have my MVP Profile of the Week returning soon.
In the meanwhile, get ready for......
We are diligently working on a super NY/NJ SQL Saturday presentation with leading SQL MVPs October 24, 2009. Prizes, software, books and sql learning on topics for DBAs. Great sponsors! Lunch will be served! FREE! Our speakers and fantastic presentations have been lined up! We are looking for another SQL Server/MVP speaker - so if you're interested, contact me immediately at rsp05@pearlknows.com or Michael Coles via LinkedIN. FULL Details and an official announcement will go up very, very soon across ALL blogs and internet sites, with info on how to register! Stay Tuned! - RP
OK, there was something that Microsoft did to tick me off. SQL Server 2005 introduced us to so many exciting and useful DMVs, such as sys.dm_os_sys_info. This DMV, which would give us a set of useful information about the computer, and about the resources available to and consumed by SQL Server, SQL 2008 came about, and already there was a change here.
One of the very useful things that we were able to derive is CPU usage. This was calculated based on the cpu_ticks and cpu_ticks_in_ms columns. The T-SQL statement that gave us the base value to successfully determine SQLProcessUtilization, OtherProcessUtilization and SystemIdle originally looked like this:
declare @ts_now bigint
select @ts_now = cpu_ticks / convert(float, cpu_ticks_in_ms) from
sys.dm_os_sys_info
However, a funny thing happened when applying the same t-sql logic in SQL Server 2008. The cpu_ticks_in_ms column is no longer present in the sys.dm_os_sys_info DMV in SQL Server 2008. Microsoft determined that this column is no longer accurate, and so, they removed it. This of course caused great consternation among the SQL Server DBA Community. Cries across the internet and sql forums everywhere, folks asked MS to please put it back! The cpu_tickes_in_ms column originally returned the number of CPU ticks in milliseconds. Due to an imprecision in this value, they could not guarantee the accurate measure. Microsoft posted the following explanation:
‘SQL 2005 used the value presented in cpu_ticks_in_ms as a conversion factor for high resolution timing. The time source used ticked at the same speed as processor instructions. SQL 2005 calculates the value assuming that CPU frequency is constant and ever increasing, however there is no accurate way to determine how many CPU ticks may actually occur in a millisecond. Current hardware that can change CPU frequency in power management modes breaks this assumption. SQL 2008 has moved to a more reliable time source, and as such no longer attempts to determine CPU frequency.’
The irony in this whole thing is that the most popular application of this DMV calculation was in their own code release of the SQL Server Performance Dashboard (SQL 2005 SP2). The stored procedure MS_PerfDashboard.usp_Main_GetCPUHistory, would fail on SQL Server 2008 with the following error:
Msg 207, Level 16, State 1, Procedure usp_Main_GetCPUHistory, Line 6Invalid column name 'cpu_ticks_in_ms'.
So, what’s a DBA to do to get the CPU utilization stats for SQL Server 2008? I didn’t want to revert back to using some WMI\VBScript code, which I found slow, and unreliable (pre-2005), and a process outside SQL Server.
After some intense scouring of the internet, and close examination of the new sys.dm_os_sys_info DMV in SQL Server 2008, I noticed the ms_ticks column, present in SQL 2005, was still there in SQL 2008. This column returned the number of milliseconds since the computer was started.
According to one article I found, ‘The first column (cpu_ticks_in_ms) remained (mostly - within 0.000001% variance which may be due to rounding errors in ms_ticks) constant (and surprise, it was roughly the CPU frequency in ms - number of CPU ticks per millisecond). Note this happened in both SQL 2k5 and 2k8. In SQL 2k5, we also added cpu_ticks_in_ms to the output and again it was within 0.000001%’
OK, close enough. By making a relatively simple modification, by applying the new logic using these columns, we can get the value using this T-SQL Code:
select cpu_ticks / ms_ticks as ratio_ticks_in_ms, cpu_ticks, ms_ticks from sys.dm_os_sys_info
The above code works on both SQL Server 2005 and SQL Server 2008. Now let’s put it all together, and get some real understandable statistics by running the following code:
set nocount on declare @ts_now bigint
select @ts_now = cpu_ticks /( cpu_ticks / ms_ticks )
/*cpu_ticks / convert(float, cpu_ticks_in_ms)*/ from sys.dm_os_sys_info
select top 1 record_id,dateadd(ms, -1 * (@ts_now - [timestamp]), GetDate()) as EventTime,
SQLProcessUtilization,SystemIdle,100 - SystemIdle - SQLProcessUtilization as OtherProcessUtilization
from (select record.value('(./Record/@id)[1]', 'int') as record_id,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') as SystemIdle,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') as SQLProcessUtilization,
timestamp from (select timestamp, convert(xml, record) as record
from sys.dm_os_ring_buffers
where ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' and record like '%<SystemHealth>%') as x
) as y order by record_id desc
Here you will get the output in a most readable format, suitable for realtime monitoring and reporting:
EventTime SQLProcessUtilization SystemIdle OtherProcessUtilization
2009-9-29 08:26:30:00.603 2 96 2
In addition to CPU Usage, you can get all sort of other great information, readily available in the sys.dm_os_sys_info DMV, including the Number of logical CPUs on the system, Ratio of the number of logical and physical processors, Amount of physical memory available, and more! (Take a look at BOL, or here for more on sys.dm_os_sys_info).
By now you know, I was using a play on words in my title, and the tick, in ‘tick me off’ was all about CPU Ticks in SQL Server’s DMV.
---------------------------------------------------------------------------------------------------------------------
For Remote DBA Services and SQLCentric monitoring, visit us at http://www.pearlknows.com
As an official ‘Friend of Red Gate’ , I occassionally get direct updates and announcements from the company. They have an excellent suite of tools, which I have used and recommended at various client sites of mine.
Although I'm not promoting specifically any one product (with the exception of my own tools from time to time :-), I wanted to just pass on the news of their new tool that can help DBA's in a pinch and recover individual database objects without the need to resort to Full backups. This can save time and resources, in production environments large and small. I believe this feature has been part of their SQL Backup Pro software, but are now offering it as a stand-alone product. Please fee free to check out below their latest offering. The product supports versions from SQL 2000, 2005 and 2008.
New Tool: SQL Object Level Recovery Native
Red Gate (www.red-gate.com) recently released a standalone tool to enable you to recover individual database objects from Microsoft® Tape Format (MTF) SQL Server backup files (.bak). It's licensed per user, so you can restore the database objects to any database on any server.
For a feature list, and on-line demos, click here to learn more about the product. Hope this helps! ---------------------------------------------------------------------------------------------------------------------
For a feature list, and on-line demos, click here to learn more about the product.
Hope this helps!
It’s time for the MVP Profile of the Week!
You’ve probably seen his work, as he’s been busy producing content for a number of web-sites, such as DatabaseJournal.com, SQL-Server-Performance.com, and of course, our beloved SQLServerCentral.com.
He’s written a lot on identifying the worst performing SQL statements, bringing us his custom stored procedures using SQL Server 2005’s DMVs and DMFs that provide detailed performance-related information – for which was featured here in SQL Server Magazine’s Reader-to-Reader column. (Don’t click yet, you’ll give it away ;-)
He is currently the lead DBA at the Washington State Department of Health and has been working with SQL Server since 1999 and other database platforms since 1985. He is also a PASS chapter leader for the Olympia Area SQL Server User Group. He currently holds a MCITP: Database Administrator and MCITP: Database Developer certificates, and is a SQL MVP. His own website http://www.sqlserverexamples.com is dedicated to providing examples of how to code T-SQL. He is, drum roll please, SQL Server MVP and TSQL extraordinaire, Greg Larsen.
Fresh back from his vacation in the wild remote wilderness of British Columbia Canada and Deer Harbor in the American San Juan islands, he’s well rested and revving up to bring us more articles, T-SQL code, training videos, and in-person presentations for our SQL DBA edification.
Greg has several training videos posted on SQL-Server-Performance.com that are featured on the main page under ‘Latest SQL Server Training Videos’. I recently viewed his training clip that introduces us to ‘Policy Based Management in SQL Server 2008’ that shows us DBAs how it can be implemented across the enterprise. Great stuff that should not be missed!
The thing that grabbed my attention the most, and put me in touch with this recently minted SQL MVP, is his creation of the awesome SQL Server DBA Dashboard, which is a homegrown report console that provides reporting and monitoring capabilities for a single instance of SQL Server 2005 utilizing the ‘Custom Report’ feature implemented with SQL Server 2005 SP2.
If you liked some of the performance dashboard stuff, put out by Microsoft, then you must check out and download the SQL Server DBA Dashboard, available for FREE on Greg’s sqlserverexamples.com website. I was truly impressed with his time and dedication to the SQL Server community, especially with his contribution of the DBA Dashboard, that I was compelled to write, and always felt that he would be an MVP someday.
So, what’s he working on right now? Never a dull moment, Greg wanted me to share the news about his upcoming SQL Saturday event where he plans to give his presentation on T-SQL Best Practices. The session gives us hands-on real-world examples of methods to write more efficient code:
“One of those things that I’m doing that give back to the SQL Server User Community is planning a SQL Saturday event. I’m right in the middle of the final planning stages of my SQL Saturday event to be held in Redmond WA, on October 3rd. This event is the second SQL Saturday event I have hosted and it has proven to exceed my expectation. Currently we have 17 different sessions planned in three different tracks. Not only are speakers coming forward but we have a number of great sponsors helping to make this event possible. Please check out our website at: http://sqlsaturday.com/eventhome.aspx for more information. It is still not too late for people to register for this event.”
If all the above wasn’t enough to keep him busy, he’s also been helping revamp the DBA SIG website which has branched out from the PASS website, so that they now have their own DNN portal. The DBA SIG site is open to the public and can be found here: http://dba.sqlpass.org/.
Commenting on his PASS Chapter (Olympia Area SQL Server User Group) that he runs, he enthusiastically urges his DBA brethren to get involved, “Attending local SQL Server user groups is a great way to learn more about SQL Server technology and network with other SQL Server professionals. If people are not attending a local PASS chapter in their area of the world they should find the closest group and start attending. It is a great resource where you can routinely talk to other people with the same problems you face on more routine timeframe then once a year at the PASS Summit (http://summit2009.sqlpass.org/AboutSummit.aspx ). People can find out more information about PASS chapters on the http://www.sqlpass.org/PASSChapters.aspx website. Running the local user group has been quite an interesting experience”
Finally, I honestly don’t know how he finds time for it all, but we should indeed be grateful for dedicated SQL Server community contributors, like Greg Larsen.
--------------------------------------------------------------------------------------------------------------------------------------
