Share via

Q&A: Performance Tuning Redefined with SQL Server 2008

  • Article

I am sure you folks are enjoying all the Q&A posted by my fellow Evangelists on this blog. The intentions are very much there to give you complete information even after our webcasts have got over. If you didnt get to read the Part I Q&A I wrote around the DMVs session, dont forget to read them too. Let me next give you the links to PPT and Session for your offline viewing if you missed them. Next, moving to the unanswered Q&A from the session. Here we go:

Question Asked: can we run data collector on sql server which is having sql server 2005 ?

The management data warehouse can only be installed on a server running SQL Server 2008.

Question Asked: different collection modes available for data collector

The Data Collector Architecture (https://msdn.microsoft.com/en-us/library/bb677355.aspx) explains the real backend work. Incidentally, this is an completely async process and uses a combination of SSIS, SQL Agent and SQL Relational engine magic. You can also manually collect and upload the data into SQL Server from the Management Studio.

Quesiton Asked: Could you plz tell something more about WMI event?

If you personally ask me, this is a super cool event one seems to miss-out from the SQL Server 2005 days itself. Anyways, check the complete documentation of WMI Events and SQL Server at: https://msdn.microsoft.com/en-us/library/ms180606.aspx. Infact, if you crank up your SQL Server Configuration Manager, it operates completely around the WMI Eventing Infrastructure. It is really powerfull and under-utilized.

Quesiton Asked: What is the Impact on Memory Usage and Performance of system of Data collection?

This my friend is a dicey question because the data collected is completely under your control. And everytime a collect and upload happens the runtime fires enough info into the database. Now when this happens is completely configurable and the purging is also configurable. Hence the stress on the IO and Memory is something in your hand. Moreover we use the SQL Server Agent infrastructure and it can also be configured to run at our specified interval rather than automatically every 10 sec or so.

Question Asked: Can we use data collector to pull the data from the the data provider or website that allows to fetch the data using webservices?

Though the question is vaugue I think if you get a chance to read through the Architecture (https://msdn.microsoft.com/en-us/library/bb677355.aspx) in detail things will get clear. There is nothing like a webservice or sorts available.

Question Asked: can we have single data collector to collect information from multiple instance ?

If you have multiple SQL Server instances you could configure the data collection processes on each of your instances to use a central MDW repository. In fact, It is recommended you do this to minimize the impact of the data collection process.

Question Asked: Can the Monitoring data be Stored outside the server being monitored

Your question seems to be similar to the one asked in the previous question. You can raise the collection as T-SQL Collector, SQL Trace, Perf Counter Collector and Query Activity Collector. You can read on each at: https://msdn.microsoft.com/en-us/library/bb677328.aspx.

Question Asked: Does it upload the data to the warehouse db on its own or we have to do it manually?

As described before, you have all the flexibility here. You can manually do it or schedule it at regular intervals via the SQL Server Agent process.

Question Asked: how much frequently is the data fed to dataware house from cache ?

Same as the previous question. You can completely customize it.

Question Asked: we can use this data collector for maintain performance related data for application or database or Visual studio application

Though this is a nice thought, currently we are restricted for collecting perf data using the DMVs inside of a SQL Server instance only.

Question Asked: Say If I run this tool, every 1 hour, for 30 days, how much of data space will it take?

There is no straight forward answer to this question. It depends on how much data you will be collecting. I have seen with an interval of 10 sec for the past ~25 days the current size is close to 400MB for me. But I this happens on my laptop which starts and shuts down often.

Question Asked: WIll Data collector effect MSDB ?

This is a good question. We do use msdb for DW activities. The data collector uses msdb for storing configuration information, run-time information, auditing, and collection history information. This database must be present on the instance of SQL Server that runs the data collector. Using msdb provides the following benefits: a) All the data needed to configure and run the data collector is in one location. b) Data collection configuration can be deployed across several servers without having to use the file system. c) The data collector can use existing SQL Server security mechanisms to protect the data. In addition, database roles can provide granular security, and there is no need to implement cross-database chaining. and finally d) Since msdb is a relational database, it is possible to ensure the referential integrity of the configuration and run-time data.

Hope these clarified some of your questions from the session and hope to meet you all again in the next VTD scheduled on Nov 25th and 26th. And guess what we will have 8 fabulous sessions around SQL Server. Dont miss the action and dont forget to register.

Comments