June 30, 2008 at 11:14 pm
Hi All,
How to get all activities on SQL server without using Profiler.
I want to know is there any alternative for sql profiler. I need the same information the sql profiler do.
I could not run sql profiler due to some constraints, I need other way around to get this informatioon.
RAM uses get higher due to some reasons I need to check that why it is getting higher and what are the activities during these times.
Thanks
http://matespoint.blogspot.com
Mohammad Irfan
http://matespoint.blogspot.com
http://www.irfit.com
June 30, 2008 at 11:55 pm
You can use the sys.dm_exec_sessions and sys.dm_exec_requests to see what's currently running on the server.
You can use the server-side trace procedures (sp_trace_*) to inplement a trace. They're the same procs that profiler uses, but you won'tr use the gui.
Why can you not use profiler?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 1, 2008 at 12:28 am
GilaMonster (6/30/2008)
You can use the sys.dm_exec_sessions and sys.dm_exec_requests to see what's currently running on the server.You can use the server-side trace procedures (sp_trace_*) to inplement a trace. They're the same procs that profiler uses, but you won'tr use the gui.
Why can you not use profiler?
Could you please let me know some link for trace example.
Mohammad Irfan
http://matespoint.blogspot.com
http://www.irfit.com
July 1, 2008 at 12:32 am
Books online has a lot of info about the sp_trace procs.
You can also use profiler to generate a script for you. Set up a trace with the events and columns that you want then go to the file ment and the the Script Trace item.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 1, 2008 at 12:45 am
Thanks a lot!!!
Mohammad Irfan
http://matespoint.blogspot.com
http://www.irfit.com
July 2, 2008 at 8:23 am
Does this server have a non-default max memory setting? You should do that for every server, and it will probably prevent the issue you are seeing.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 2, 2008 at 9:07 am
If your environment is MS SQL 2005 + SP2, the best choice is SQL Server 2005 Performance Dashboard. It is a free download software.
http://www.sql-server-performance.com/articles/per/bm_performance_dashboard_2005_p1.aspx
July 2, 2008 at 10:44 pm
rw_ebox (7/2/2008)
If your environment is MS SQL 2005 + SP2, the best choice is SQL Server 2005 Performance Dashboard. It is a free download software.http://www.sql-server-performance.com/articles/per/bm_performance_dashboard_2005_p1.aspx
Cant do anything on server, its a production server.
Mohammad Irfan
http://matespoint.blogspot.com
http://www.irfit.com
July 3, 2008 at 6:42 am
Mohammad Irfan (7/2/2008)
rw_ebox (7/2/2008)
If your environment is MS SQL 2005 + SP2, the best choice is SQL Server 2005 Performance Dashboard. It is a free download software.http://www.sql-server-performance.com/articles/per/bm_performance_dashboard_2005_p1.aspx
Cant do anything on server, its a production server.
That statement doesn't make much sense to me. You simply MUST be able to properly monitor and tune a production server. To do otherwise is just throwing money away.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 3, 2008 at 8:54 am
If you just want to run some SQL queries, please check Microsoft web site, "SQL Server Best Practices".
http://technet.microsoft.com/en-ca/sqlserver/bb331794.aspx
SQL Server Best Practices Toolbox
Scripts and Tools for Performance Tuning and Troubleshooting SQL Server 2005
Buffer Cache
CPU and Optimization
Indexes and Indexing
Input/Output
Performance
Query Plan Re-Use
Retrieving SQL Text and XML Plans
SQLOS: Schedulers, Runnable Queue, Waiter List
Tempdb
Transactions and Locking
Wait Statistics
SQL Server 2005 Best Practices Analyzer
DMV Stats
July 16, 2008 at 12:51 am
Hi,
without profiler the only option is by clicking the REPORT tab in SSMS u will get all activities of server, its performance and many lot.:)
August 20, 2008 at 1:07 pm
Linchi Shea has a good write up and example on using sp_trace at SQLblog.com:
http://sqlblog.com/blogs/linchi_shea/archive/2007/08/01/trace-profiler-test.aspx
It also shows how server trace and profiler impact the server.
😎
The more you are prepared, the less you need it.
August 20, 2008 at 11:34 pm
Thanks An
Mohammad Irfan
http://matespoint.blogspot.com
http://www.irfit.com
September 18, 2008 at 9:51 am
Hi All,
I ran into the same situation. I was assigned to identify and remedy poor performing queries on an extremely heavy use production server. First I was using SQL Profiler in combination with Database Tuning Advisor using Tuning predefinied template to monitor bad SP, T-SQL etc... CPU, read, write trying to get the hint toward a solution. But my boss and the IT team ordered me to stop that because it adds more overhead, performance penalty to already heavy-use production server, degrading the performance more... I know I can use DMV, DMF ...but as far as I know, SQL profiler is the best tool to diagnose performance....
Now, I'm not allowed to use SQL profiler, anyone knows a tool, another way to substitute SQL profiler without incurring more overhead on the production server.
Thanks.
David N Nguyen
September 18, 2008 at 10:06 am
Use the server-side trace functionality. It's what profiler uses behind the scenes and it uses a bunch of sp_trace procedures.
You can get profiler to create a script for you (the best way), then modify and run that on the server. Provided you're writing to a fast drive that doesn't contain any of the database files, the overhead should be a lot, lot lower than that of the Profiler GUI.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply