how to use SQL profiler to check SQL query overloading

Dear all,

some experienced programmer in our company said they use SQL profiler to check the bottleneck of SQL loading on that SQL box to see:

1) is there any network content from SQL client to SQL server.
2) is there any SQL call loading from SQL client to SQL server.
3) Transaction per sec can go inside the SQL server.

how can SQL profile help on this?
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vadim RappCommented:
1) Profiler can show SQL requests coming from workstations to the server, and you can filter them by workstations. It will show the query, user, and workstation it came from, among many other things.

2) Not sure what you mean by call loading.

3) Transactions per second are better to view by using Performance Monitor, it's one of the standard counters under object "SQL Server: Databases".
marrowyungSenior Technical architecture (Data)Author Commented:
"1) Profiler can show SQL requests coming from workstations to the server, and you can filter them by workstations. It will show the query, user, and workstation it came from, among many other things.
"

the sys.sysprocess can do this too!

"2) Not sure what you mean by call loading."

I mean SQL query over loading and what makes that out !

"3) Transactions per second are better to view by using Performance Monitor, it's one of the standard counters under object "SQL Server: Databases"."

SQL profiler can't measure that out for us ?
Vadim RappCommented:
We suggest that you simply run it and see what it does and how.
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

marrowyungSenior Technical architecture (Data)Author Commented:
you mean how to run SQL profiler? I usually use SQL profiler to detect deadlock !

what do you want me to check and see ?

any URL on the advance use of SQL profiler?
Vadim RappCommented:
> you mean how to run SQL profiler?
yes

> I usually use SQL profiler to detect deadlock !
I was under impression that you are not familiar with it at all. If this is not the case, and you know how it works and what it shows, then I don't quite understand what you are asking.

1) is there any network content from SQL client to SQL server.
Profiler shows every query that comes from sql server clients to the server.

2) is there any SQL call loading from SQL client to SQL server.
Profiler allows you to filter the queries that take long time or lots of i/o. So if you already know how to filter Profiler's output for deadlocks, in the same way you can filter for the queries that run, for example, more than X seconds. See this article, for example.

3) Transaction per sec can go inside the SQL server.
You can filter for transactions, use stopwatch, calculate how many will be per minute, and divide. But it's probably much more convenient to do by Performance Monitor.
marrowyungSenior Technical architecture (Data)Author Commented:
"2) is there any SQL call loading from SQL client to SQL server.

Profiler allows you to filter the queries that take long time or lots of i/o. So if you already know how to filter Profiler's output for deadlocks, in the same way you can filter for the queries that run, for example, more than X seconds. See this article, for example.
"
yeah, I knew, but seems can't dig out more information than that.

"3) Transaction per sec can go inside the SQL server.

You can filter for transactions, use stopwatch, calculate how many will be per minute, and divide. But it's probably much more convenient to do by Performance Monitor."

yeah...
marrowyungSenior Technical architecture (Data)Author Commented:
just want to use SQL profiler to find out what else I can do other than all these above.
Vadim RappCommented:
The only not-yet-mentioned feature of it is to save sql statements in one server, then replay them on another.

Here's ebook "Mastering SQL Profiler", it's safe to say there's not much else than what's described in it. Note the chapter about correlating Profiler's data with Performance Monitor, maybe that's what you are looking for in your #3.
Vitor MontalvãoMSSQL Senior EngineerCommented:
just want to use SQL profiler to find out what else I can do other than all these above.
If you're going to start then start with something more recent since SQL Profiler will be deprecated in the next SQL Server version.
Check for SQL Extended Events and SQL Audit since they perform better.
marrowyungSenior Technical architecture (Data)Author Commented:
Vadim Rapp,

"The only not-yet-mentioned feature of it is to save sql statements in one server, then replay them on another."

good! but since SQL 2012, it has SQL replay client and replay server and I think they are doing that already ?

"Here's ebook "Mastering SQL Profiler", it's safe to say there's not much else than what's described in it. Note the chapter about correlating Profiler's data with Performance Monitor, maybe that's what you are looking for in your #3."

tks and I will take a look.

Victor,

tks again for join my ticket.

"Check for SQL Extended Events and SQL Audit since they perform better."

that one is more on SQL action auditing side rather that data auditing side, right? it is designed for someone who only has SQL server standard edition but not Enterprise edition, right? but SQL audit only available from Enterprise edition.

But what I want to know is:

2) is there any SQL call overloading from SQL client to SQL server.
3) Transaction per sec can go inside the SQL server.

our experienced developer can user some information from SQL profiler to know that, for 100Mpbs network, max transaction per sec from SQL client to SQL server will be 1000 transaction.

for 1Gbps network, it will be around 2300 transaction/sec, that's what i want to know how to use SQL profiler to know.

might be the transcation/sec can only use performance monitor to see but how about SQL call (no matter it is SQL  SP call or SQL function call or general SQL T-SQL query) is overloading ?
Vadim RappCommented:
I don't think there can be some hard numbers equally good for everybody. it depends on many variables, including how long your clients are willing to wait for the response. What you are looking for is probably stress testing.
marrowyungSenior Technical architecture (Data)Author Commented:
so from all of your statement, SQL profiler can't do it ?

" including how long your clients are willing to wait for the response. "

they will say I don't want to wait !
marrowyungSenior Technical architecture (Data)Author Commented:
did you use this before: http://www.sqlstress.com/

?
Vadim RappCommented:
> so from all of your statement, SQL profiler can't do it ?

I think, we have provided more than enough material about what it can do. Did you check it out?
Vitor MontalvãoMSSQL Senior EngineerCommented:
that one is more on SQL action auditing side rather that data auditing side, right?
No. Extended Events are the natural replacer for SQL Trace. Here's a very good article on how to work with Extended Events.

it is designed for someone who only has SQL server standard edition but not Enterprise edition, right? but SQL audit only available from Enterprise edition.
Yes and No. SQL Audit is available in Standard Edition but only for server level audit. Database level audit is only available in Enterprise Edition.
marrowyungSenior Technical architecture (Data)Author Commented:
"Yes and No. SQL Audit is available in Standard Edition but only for server level audit. Database level audit is only available in Enterprise Edition."

I tried that only C2 audit can be turn on, not the rest of the method, it will say it will enterprise edition to enable the rest !
marrowyungSenior Technical architecture (Data)Author Commented:
I just want to use SQL profiler to check out potential performance SQL problem.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Ok, looks like you already have your mind set to SQL Profiler so can't do nothing but recommend you this old but good article for use SQL Profiler.
Good luck.
marrowyungSenior Technical architecture (Data)Author Commented:
nono, I listen to you and I also bear in mind that my developer might lies to me too !

they keep saying this.
marrowyungSenior Technical architecture (Data)Author Commented:
any URL on advance use of SQL profiler ?

you will not recommend books as this product will be retire soon ?

from your point of view, is it good to retire this product but replace with extend event ?
Vitor MontalvãoMSSQL Senior EngineerCommented:
any URL on advance use of SQL profiler ?
I don't think there's nothing advanced with SQL Profiler. Did you check the link from my previous comment?

you will not recommend books as this product will be retire soon ?
I don't know any books. Internet are my best friend on this ;)

from your point of view, is it good to retire this product but replace with extend event ?
Not from my point of view but Microsoft's. SQL Profiler it's a very old technology (more than 15 years) so it's time for a refresh. Extended Events is the natural replacer.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
marrowyungSenior Technical architecture (Data)Author Commented:
"I don't think there's nothing advanced with SQL Profiler"

there is advanced user with SQL profiler, right?

"Extended Events is the natural replacer."

tks.
marrowyungSenior Technical architecture (Data)Author Commented:
tks all for it and all of your link was read,
marrowyungSenior Technical architecture (Data)Author Commented:
hi all,

I found that if I type cursor and while in the profiler filter, it still give me sth else more.

I just want to search for the query still using cursor and while loop, what should I type in the filter condition ?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.