I have a question for a DBA who are in Sajha
How you monitor/Tune performance in Production Server
note: In production server, we don't have facility called Auto_update_statistics, So there is no use of DMV/DMF's I think..Guessed So!.but not sure. and Production server HAVE to be online for 24/7.

I Figured out one way. which is connecting the production server from different manchine and run sql profiler. Can you suggest any other method?

Hi Virusno1,

Good question! Looks like you are talking about SQL server. It's hard to hit you back with the right answer since question itself is not that clear to me. First of all,  I just want to know if you are using 2000, 2005 or 2008, since lots of new techniques has been added. I don't think its a best idea to run profiler all the time since it will slow down the server.  You can create and schedule a job to run DBCC dbreindex or update statistics and run it when server usage is minimal. You can even used 3rd party tools like quest's spotlight, Foglight etc to monitor and tune database.

However, if you are talking about slowing down of particular query, you might have to do extra sit ups to find out what exactly is slowing down, are there too many joins, have lots of data been changed lately, are  your tables heavily fragmented, have your query using any sproc etc.

Once again, these are not all the possible answers. Do you have problem with the server itself? Does you server has enough memory? Is sql server configured to use available memory?  I don't think sajha will be the best site if you are really looking for answer. Please, try to go to microsoft's website or Sq-server-performance.com etc. where you can meet  with people who had given there lives to these technologies.

I hope this at least helped you a little bit. I wish you all the best.

Thanks

Instead of SQL Profiler, You can use Server-side trace which can be scheduled and less resource intensive.Here is my script for Server-Side trace :

--Stored Procedure to run trace for 30 minutes
Create Procedure dbo.Usp_Tracefor30minutes
as
set nocount on

declare @stop datetime
set @stop = dateadd(mi,30,getdate())
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 50
-- create traces and saves in folder specified
exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, @stop
if (@rc != 0) goto error
-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 17, 12, @on
exec sp_trace_setevent @TraceID, 17, 1, @on
exec sp_trace_setevent @TraceID, 17, 9, @on
exec sp_trace_setevent @TraceID, 17, 6, @on -- 17 - Existing Connections
exec sp_trace_setevent @TraceID, 17, 10, @on
exec sp_trace_setevent @TraceID, 17, 14, @on
exec sp_trace_setevent @TraceID, 17, 11, @on
exec sp_trace_setevent @TraceID, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 1, @on -- 10 - RPC:Completed
exec sp_trace_setevent @TraceID, 10, 17, @on
exec sp_trace_setevent @TraceID, 10, 2, @on
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 18, @on
exec sp_trace_setevent @TraceID, 10, 11, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 13, @on
exec sp_trace_setevent @TraceID, 10, 6, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
exec sp_trace_setevent @TraceID, 43, 15, @on
exec sp_trace_setevent @TraceID, 43, 1, @on
exec sp_trace_setevent @TraceID, 43, 9, @on -- 43 - SP:Completed
exec sp_trace_setevent @TraceID, 43, 10, @on
exec sp_trace_setevent @TraceID, 43, 11, @on
exec sp_trace_setevent @TraceID, 43, 12, @on
exec sp_trace_setevent @TraceID, 43, 13, @on
exec sp_trace_setevent @TraceID, 43, 6, @on
exec sp_trace_setevent @TraceID, 43, 14, @on
exec sp_trace_setevent @TraceID, 37, 1, @on
exec sp_trace_setevent @TraceID, 37, 9, @on
exec sp_trace_setevent @TraceID, 37, 6, @on -- 37 - SP:Recompile
exec sp_trace_setevent @TraceID, 37, 10, @on
exec sp_trace_setevent @TraceID, 37, 14, @on
exec sp_trace_setevent @TraceID, 37, 11, @on
exec sp_trace_setevent @TraceID, 37, 12, @on
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 1, @on -- 12 - SQL:BatchCompleted
exec sp_trace_setevent @TraceID, 12, 9, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 6, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 18, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on
exec sp_trace_setevent @TraceID, 13, 12, @on
exec sp_trace_setevent @TraceID, 13, 1, @on
exec sp_trace_setevent @TraceID, 13, 9, @on -- 13 - SQL :BatchStarting
exec sp_trace_setevent @TraceID, 13, 6, @on
exec sp_trace_setevent @TraceID, 13, 10, @on
exec sp_trace_setevent @TraceID, 13, 14, @on
exec sp_trace_setevent @TraceID, 13, 11, @on
 
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler'
set @bigintfilter = 5000
exec sp_trace_setfilter @TraceID, 13, 0, 4, @bigintfilter
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1
-- display trace id for future references
select TraceID=@TraceID
goto finish
error:
select ErrorCode=@rc
finish:
go

You can find me in SQLteam.com under name sodeep. There are lots of microsoft MVP and we deal with these issues on regular basis.

 

Thanks smoothcriminal. But I have few questions. Can you tell me steps to test the performance in PRODUCTION server. I am completely aware that we shouldn't put loads on production server by using sql profiler but I am curious to know your practice while you worked on production server for performance test. Is Auto_update_statistics is turned on your server coz I heard it put locks on tables.

Yes, you are absolutely correct that you shouldn't run SQL profiler on Remote server for long time. That's why I referred you server -side trace where you choose only events and filters you are looking for. We leave Auto_create and Auto_update statistics on all production server as it lets when to create stats and update stats . It is absolutely wrong that it locks table. I think you are talking about Rebuilding index.

Here is the main point:

1) When you are rebuilding Clustered  with DBCC DBREINDEX or ALter index...rebuild, it locks table as it has to drop index and recreate with new clustered index matching clustered key.It updates statistics also with full scan

2) But when you are REORGANIZING index , it doesn't lock table and it doesn't update stats

3)In  SQL 2005, instead of runing  Perfmon or Profiler, you can see status of server and Performance of query with  Standard reports that comes with SQL Server.

 

 

I have another question.

If we are using Performance Monitor, then what are the most common counters that you watch? Are there any Benchmark for those counters to check, whether they are performing optimally or not?

Hey,

about the performance monitoring and its issues, it would be better to go through this white paper of microsoft:

http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx

This paper has the possible issues and standards for troubleshooting and monitoring the perfromance of SQL Server 2005.

Hope it will help u.