Using Analysis Services To Analyze SQL Server Acti

67 %
33 %
Information about Using Analysis Services To Analyze SQL Server Acti

Published on January 9, 2008

Author: Sigfrid


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 :

Add a comment

Related presentations

Related pages

SQL Server Analysis Services

Microsoft SQL Server 2005 Analysis Services (SSAS) delivers online analytical processing (OLAP) and data mining functionality for business intelligence ...
Read more

Analyze data from SQL Server Analysis Services by using Excel

... imported from Microsoft SQL Server 2008 Analysis Services ... analyze sales by using a ... steps required before any analysis ...
Read more

Analysis Services Tutorials (SSAS)

Get started with Datazen dashboards using Analysis Services data ... Tutorials that also work with a SQL Server 2014 instance of Analysis Services. ...
Read more

How to configure SQL Server 2008 Analysis Services and SQL ...

How to configure SQL Server 2008 Analysis Services and SQL Server 2005 Analysis Services to use Kerberos authentication
Read more

Microsoft SQL Server 2008: Analysis Services

Microsoft SQL Server 2008 helps enable ... multidimensional analysis and reporting to ... solutions by using Analysis Services Dynamic Management ...
Read more

Using SSAS to analyze sql server performance

Using SSAS to analyze sql server performance. SQL Server > SQL Server Analysis ... now what you want is using SQL Server Analysis Services, ...
Read more

Connect to or import data from SQL Server Analysis ...

Connect to or import data from SQL Server Analysis Services. ... if the workbook is saved to Excel Services and is opened by using Excel Services, ...
Read more

Microsoft Analysis Services - Wikipedia, the free encyclopedia

Microsoft SQL Server Analysis Services, ... SSAS is used as a tool by organizations to analyze and make sense of ... and indexed using a special format ...
Read more

Analysis Services

Analysis Services is an online analytical data engine used in decision support and data analytics, ... (Analysis Services) SQL Server Resource Center ...
Read more