As database professionals, we have a need to benchmark performance of the database, processes, and essentially overall performance. When benchmarking, it is preferable to get a baseline and then run the same benchmark tests on a periodic basis and compare those results to the baseline.
Recently I was reminded of a couple of tools that should be in every DB Professionals vocabulary and tool set. Each one is used for a different purpose. Those tools are:
CPU-Z is a freeware app that helps you gather information about motherboard, CPU, and memory. It helps you to determine processor usage and if you are running your hardware at an optimal level.
TPC-E simulates the OLTP workload. It is designed to be representative of OLTP systems and is scalable.
Both of these tools can be of great use to the database professional. Check them out and see what you can learn by using them.
How many DB professionals have never had to deal with bitwise operations in SQL Server? Who has never had a single value in the database represent more than one data value? Have you ever had one of these fields serve as the(implicit or explicit) foreign key to a source table?
Sometimes it can seem a bit tricky dealing with such data – especially the last. I’m not going to delve into the complexity of such scenarios. I do want to present a basic intro however.
There are three basic operators for bitwise operations. The operators are &, |, and ^. These operators perform logical operations against integer type data. Here is an example of what each would return when using the same values:
SELECT 175&75 AS BitAnd, 175|75 AS BitOr, 175^75 AS BitXOr
BitAnd BitOr BitXOr
11 239 228
When performing a Bit & operation, the bits in the result are set to 1 if and only if both bits (for the current bit being resolved) in the input expressions have a value of 1. Any other combination results in a 0.
When performing a Bit | operation, the bits in the result are set to 1 if either of the corresponding bits is a 1. A 0 is returned when both values are 0 in the corresponding bit.
When performing a Bit ^ operation, the bits in the result are set to a value of 1 if 1 of the two bits (but not both) has a value of 1. Any other combination (either all 0s or all 1s) will result in a value of 0.
As I work with this a bit, I will be sharing a particular scenario that has been quite interesting for me.
I often see a request for some scripts to help with database maintenance. Sometimes those questions come in the form of recommendation requests for maintenance plans. As many already know, there are some really good recommended scripts by Ola Hallengren. There is also the fantastic index defrag script by Michelle Ufford.
Under normal circumstances, my recommendation would be that the maintenance of your database depends on your environment. However, that doesn’t always work well. Sometimes, something is needed quick to get up and in place while you figure things out in your environment and for your database (let’s say you just started a new job or you just inherited the database duties).
Under such circumstances, it would be good to have something ready to go already. Thus, I recommend using these resources and learn from them.
Index Defrag Script by Michelle
Maintenance Scripts by Ola
This is just a quick reminder about the meeting coming up on March 10, 2011 at 6:30 PST. You can find more information about the event from here.
I am looking forward to this presentation. I think this presentation can be highly useful for both Production DBA as well as Development DBA. Please join us Thursday evening to get some good information. Anybody who wants to attend is welcome to attend – whether from Vegas or not. (I will be attending afterall and currently do not live in Vegas.)
We will be recording the meeting and providing a link to it after the meeting. So if you can’t make it, you may still be able to access it.
I hope to see you there (well, virtually anyway).
Last Year I introduced a couple of scripts that I worked on. Those scripts developed into a series covering the comparison of some methods and MS provided stored procs that could help you in the retrieval of table sizes in SQL Server.
I pulled out one of those scripts recently in order to find what tables, in an R and D database, were consuming alot of space. While running that script, I realized that it could easily be used to help me with an additional task. I decided that this script could help me deduce the top 10 biggest tables in a database that doesn’t necessarily need to have that data. Or maybe, I just need to clean out the data so I can test populating the database. This script is predicated on a lack of foreign keys – but can easily be adapted.
So, in all of its glory:
/*similar to sp_spaceused */
DECLARE @dbsize DECIMAL(19,2)
SET NOCOUNT ON
** Summary data.
SELECT @dbsize = SUM(CONVERT(DECIMAL(19,2),CASE WHEN type = 0 THEN SIZE ELSE 0 END)) * 8/1024
, @logsize = SUM(CONVERT(DECIMAL(19,2),CASE WHEN type = 1 THEN SIZE ELSE 0 END)) * 8/1024
** We want all objects.
WITH FirstPass AS (
ReservedPage = CONVERT(DECIMAL(19,2),SUM(reserved_page_count)) * 8/1024,
UsedPage = CONVERT(DECIMAL(19,2),SUM(used_page_count)) *8/1024,
PageCnt = SUM(
WHEN (index_id < 2)
ELSE lob_used_page_count + row_overflow_used_page_count
)) * 8/1024,
RowCnt = SUM(
WHEN (index_id < 2)
--Where OBJECTPROPERTY(OBJECT_ID,'IsMSShipped') = 0
GROUP BY OBJECT_ID
,InternalTables AS (
ReservedPage = CONVERT(DECIMAL(19,2),SUM(reserved_page_count)) * 8/1024,
UsedPage = CONVERT(DECIMAL(19,2),SUM(used_page_count)) *8/1024
FROM sys.dm_db_partition_stats ps
INNER Join sys.internal_tables it
ON it.OBJECT_ID = ps.OBJECT_ID
And it.internal_type IN (202,204,211,212,213,214,215,216)
WHERE it.parent_id = ps.OBJECT_ID
--And OBJECTPROPERTY(ps.OBJECT_ID,'IsMSShipped') = 0
GROUP BY ps.OBJECT_ID
SELECT 'Truncate table ' + OBJECT_NAME (f.OBJECT_ID) + '' AS TruncTabStatement
,TableSizeMB = SUM(f.PageCnt) +
SUM(CASE WHEN (f.UsedPage + IsNull(i.UsedPage,0)) > f.PageCnt
THEN ((f.UsedPage + IsNull(i.UsedPage,0)) - f.PageCnt) ELSE 0 END)
FROM FirstPass F
LEFT Outer Join InternalTables i
ON i.OBJECT_ID = f.OBJECT_ID
WHERE OBJECTPROPERTY(f.OBJECT_ID,'IsMsShipped') = 0
GROUP BY f.OBJECT_ID
ORDER BY TableSizeMB DESC
I think the script, along with prior explanations, is pretty straight forward. This can quickly help reset those LARGE tables for continued testing. Of course, that is predicated on you not already having a script for that, and that you don’t know what tables need to be reset (maybe you are in a large team).
This is just one example of a script that can be useful for more than what it was first designed to do. As DB professionals, we often come across situations were a prior script can easily be repurposed for the task at hand. Knowing that, and how to do it, is an important tool in your toolbox.
The Ides of March are upon us and that means that we have another opportunity to learn some stuff about SQL Server. We have that opportunity because it is time for the monthly S3OLV User Group meeting.
This month, Glenn Berry of SQL Server MVP fame has volunteered to present to our group via Livemeeting. Glenn will be teaching us how to properly Select and Size database hardware in regards to OLTP performance. Here is the abstract for this presentation.
The foundation of database performance is the underlying server hardware and storage subsystem. Even the best designed and optimized database application can be crippled by an inadequate hardware and storage infrastructure. Recent advances in new processors and chipsets, along with improvements in magnetic and SSD storage have dramatically changed the evaluation and selection process compared to the past. Many database professionals struggle to keep up with new technology and often simply let someone else make their hardware selection and sizing decisions. Unfortunately, the DBA usually gets the blame for any performance issues that crop up later. Don’t let this happen to you! This session covers current and upcoming hardware from both Intel and AMD and gives you the tools and resources to make better hardware selection decisions to support SQL Server OLTP workloads.
We will be holding this learning opportunity March 10, 2011 between 6:30 PM PST and 8:30PST.
Livemeeting Connection Information can be found as follows:
Copy this address and paste it into your web browser: https://www.livemeeting.com/cc/UserGroups/join
Copy and paste the required information: Meeting ID: 3DPBZQ
A little about our Presenter
Glenn works as a Database Architect at NewsGator Technologies in Denver, CO. He is a SQL Server MVP, and he has a whole collection of Microsoft certifications, including MCITP, MCDBA, MCSE, MCSD, MCAD, and MCTS, which proves that he likes to take tests. His expertise includes DMVs, high availability, hardware selection, full text search, and SQL Azure. He is also an Adjunct Faculty member at University College – University of Denver, where has been teaching since 2000. He recently completed the Master Teacher Program at Denver University – University College. He is the author of two chapters in the book SQL Server MVP Deep Dives, and blogs regularly at http://sqlserverperformance.wordpress.com.
Well, we are coming around into a new year. With this new year there is once again PASS Summit preparations that are well under way. Time has come again for people to volunteer to help with PASS Summit. The call has gone out!! Allen Kinsel has blogged about it here.
For those that haven’t volunteered or helped in the past, it doesn’t require too much and there are many ways in which you can help. Assistance is needed from abstract review to speaker ratings to some database work to being an usher during the Summit. PASS has put together a little survey to help you in volunteering for the Summit 2011. You can find the survey here.
So what do you get by volunteering to help at PASS? You get a sense of accomplishment for having helped, in whatever miniscule way, to put on a pretty big Conference for the best Community out there – the SQL Server Community. I liked helping out last year and have volunteered again. I hope to be selected and wouldn’t mind doing something different this year. On the flip-side, I wouldn’t mind doing the same things I did last year.
I know, this wasn’t much of a motivational speech. Hopefully this works better for you – JUST DO IT.