Using Analysis Services To Analyze SQL Server Activity: Using Analysis Services To Analyze SQL Server Activity A brief overview on how to quickly create an excellent log of SQL Server Activity & resource utilization. Presented by : Brian Flynn, DBA,Developer


My Background: My Background Saint Louis University Graduate Computer Science / Mathematics Web Design/Development Began studying & practicing web design/development in 1996. Taught web design/development and held a career job as a designer/developer from 1999-2002. Entrepreneur While in college, started a business with a friend to gain web design/development experience. Used to wearing lots of hats and believe in being well rounded vs. over-specialized. SQL Server Began working with SQL Server while working as a web developer and became interested in it as a new area of expertise. In 2002 took a job with Stifel Nicolaus with the primary responsibility being SQL Server DBA with a secondary responsibility as an IIS admin & liaison to web developers for the systems engineers. In 2006 took a newly created DBA position with Gateway EDI


My Philosophy: My Philosophy Nobody tries to fail I strive to help 2 groups better understand each other that often end up at odds, the administrators & the developers. Don’t just tell them, SHOW THEM! Many people assimilate information provided in visual material such as charts & graphs better than being told the same thing verbally or in writing. I don’t want anyone to resent me, I want them to admire me and desire my input. I remember being a developer and resenting condescending & uncooperative administrators.


Tools I Use For This Technique: Tools I Use For This Technique SQL Server 2000/2005 Analysis Services 2000/2005 Excel 2000/2003/2007 MsPaint I assume you already have these? ;-) If your situation is like mine, you hesitate to suggest to your boss that the company spend more money.


Capture CPU & PIO Every Min: Capture CPU & PIO Every Min


Calculate Deltas in a View: Calculate Deltas in a View Create a “Delta View” One of the secrets to creating a good cube based on CPU & PIO data recorded every minute is calculating the deltas. To match up from one minute to the next, use LoginTime, ProcessID & ContextID together. Look out for NULLs


Scheduled Job(s): Scheduled Job(s) Single Server vs. Multi-Server Setup Depending on if you do everything on one server or only gather data on the production server & use a separate server to process the data will determine your specific job definition(s). Gather data for 15 minutes, incrementally update cube & restart the gathering.


SQL 2000 Cube: SQL 2000 Cube Cubes designs can vary. I’ve used the same basic cube structure since 2001. This is a recent mod on that design I made last year to accommodate the monitoring of multiple servers in a single cube.


SQL 2005 Cube: SQL 2005 Cube I’ve only recently ported this to 2005. I found a need to modify how I manage the Time Dimension.


Slide Show: Slide Show View slides of Process Info Log in Practice.


Capture Disk Space Used Daily: Capture Disk Space Used Daily Loop over every table in every database and record the results of sp_spaceused @objname = ‘MyTable’


Calculate Deltas in a View: Calculate Deltas in a View You can create cubes that work off of the data gathered as is as well as one based on deltas. Define a view that can present delta values by day. Look out for negative values & NULLS. I haven’t perfected this process, but I’ve often found that zeroing them out has produced more sensible graphs in my experience. I’m assuming those values are the result of the values in sysobjects decreasing in value after optimizations.


Non-Delta Cube: Non-Delta Cube The secret to the Non-Delta Cube is to set the aggregate Function to Max & only view at the Table Level.


Delta Cube: Delta Cube In the delta cube, since we’re aggregating deltas, the aggregate function works well as sum no matter what level of the ObjTree dimension you view with.


Slide Show: Slide Show View Slide Show Disk Space Cubes in practice.


The Sky’s The Limit!: The Sky’s The Limit!


Thank You!: Thank You! Thank you for the opportunity to share my creation with you! I hope you find it useful. Email : bflynn@battech.com