New Speaking Event... Confio Webinar in May

Indexing for SQL Server Performance – Smart Practices for the DBA and Developer http://ow.ly/kj9Ug 

Date: Thursday, May 9, 2013
Time: 2:00pm ET/7:00pm GMT
Duration: 1 hour

I hope to see you there!

SQL Saturday, San Juan, 4/20/2013

Come say Hi!

How many rows are in your tables?

If you need counts on all of your tables (perhaps you are doing some data mining on a new server?), you probably don't want to run a "selcet count (*)" on all the tables -- it's time consuming and resoruce intensive.

Instead, try this:

SELECT sc.name +'.'+ ta.name TableName

 ,SUM(pa.rows) RowCnt

 FROM sys.tables ta

 INNER JOIN sys.partitions pa

 ON pa.OBJECT_ID = ta.OBJECT_ID

 INNER JOIN sys.schemas sc

 ON ta.schema_id = sc.schema_id

 WHERE ta.is_ms_shipped = 1 AND pa.index_id IN (1,0)

 GROUP BY sc.name,ta.name

 ORDER BY SUM(pa.rows) DESC

SQL Saturdays...

Looks like I'm going to be speaking at SQL Satuirday in Tampa in only a few weeks; then in Jacksonville a couple of months later; and possibly, in San Juan in between. (Also thinking about Manhattan in August... )

Be sure to come over & say "Hi!"

(Register here:  www.sqlsaturday.com )

performance?

A recent forum post pushed enough of my buttons that I chose to respond, and the topics themselves were interesting, so I'm copying the post here.

 

1) Science vs. Art

People frequently define tuning as an art rather than a science, but what is scientific method? You postulate a theory, test it, and repostulate if necessary. That's what we do when we tune.

 

2) What's a valid baseline?

I remember shocking a group of about 100 recent IVY grads at a talk I did a while back when I stated that business is not like a college exam; there is no "right" or "wrong," there is instead "the solution works" or "the solution doesn't work" ... there may be multiple correct (i.e. working) answers.

In this case, you need to be able to answer questions along the lines of "Are all of the potential bottlenecks on our system wide enough to accommodate the current expected usage and identified expected growth?" If the answer is yes, you're in good shape. If you're looking for harder numbers, my personal preference is not to exceed 70% of resources at normal peak demand; if you are, then you've got room for unexpected peaks; if you're above 80%, you need to tune or shop. (this is a VERY general rule and needs to be applied with knowledge of your system of course.)

If you can't answer these questions you need to reexamine your work so that you can do predictive analysis

3) Where do we start tuning?

About 6-7 years ago, after doing little but tuning for 15 years, I've changed my approach. Before interviewing users about their issues, or looking at perfmon which gives us a snapshot view of a very tight time slice, I install a tool (the one I currently use is Confio Ignite -- subjectivity warning, here, as we resell this tool, but I examined a dozen others before choosing Ignite). After the tool starts collecting, I interview the users about what they think their problems are, and subsequently start looking at what the tool is collecting, starting at a macro scale and moving from there to the micro scale based upon findings at a higher level; this may lead me to anything from disk issues to specific queries run by specific users at specific times of the day.

 

Shrinking the transaction log

Every once in a while I look something up & want to keep referencing it... this article is one of those, I hope you find it as useful as I did:

http://www.mssqltips.com/sqlservertip/2097/how-to-shrink-the-transaction-log-file-in-sql-server-2008/

Transaction Log Space Utilization

A client asked me to investigate log space utilization on a specific database . It was  currently allocated at 53 gig, and the question was whether that was big enough or too big. I gave a pretty generic answer, which is that it needs to be big enough to contain all active transactions between dumps & during replication, plus a fudge factor. There’s a GREAT article here:

 

http://www.mssqltips.com/sqlservertip/1225/how-to-determine-sql-server-database-transaction-log-usage/

it also shows some loosely documented /undocumented commands to take a look.

At the time, they were using .22% (Yep, less than 1%) of the 53 gig transaction log. It’s impossible for me to tell how big it needs to be (without a deep understanding of the application, but a potential guideline is 25% bigger than the biggest transaction log backup you have; so, if those are in the 1-2 gig range, they had a lot of room to spare here.

Note that with SQL 2005 (their version), you can not shrink the log file below its initial size (this is corrected in SQL 2008 & later).