asked on

How to find out who is running lot of database query in SQL

Hi

I have a customer running SAP with SQL 2008 R2.
Their SAP hang intermittently and whenever it hang, it back to normal once I restart SQL service.

There are also user who knows how to generate report in Excel by connecting to SQL server and they sometimes generates the reports in excel.

I'm thinking SAP hang because of SQL server consumes lots of memory because i can see memory usage is always high in task manager.
If I limit the memory of SQL server too, I think it will still hang when it requires more memory.

So I'm thinking to suggest my customer to upgrade RAM but I must make sure it hang because of memory.

How can I identify which user SAP id or computer is the one running lots of SQL query (either using SAP or excel) ?

Any of you encounter such issue ? The server is 2008 R2 with SQL 2008 R2. Ram is 32 GB and 31GB is always in used(but I think it is normal in SQL server installed server). SQL process consumes about 28GB all time.

What could be the root cause that cause SAP to hang ? At the moment I just restart SQL service every morning as temporary solution. Any good suggestion is appreciated

Thanks

ASKER CERTIFIED SOLUTION

Pavel Celba

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bominthu

ASKER

It is not always hang. Like I mentioned SAP hang intermittently and once it hang, it back to normal as soon as I restart SQL service.

I think it is because of some users generating lots of report using Excel that connects to SQL server.

So possible to identify from which user machine that running a lot of SQL query ?
All users machines are member of domain.

SOLUTION

Qlemo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Pavel Celba

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

grendel777

First I'd run sp_who2 in SQL Management Studio to get a feel for the database connections. This will help you sort and filter the results:

DECLARE @tbl TABLE
(
	SPID1 INT, 
	STATUS VARCHAR(255),	
	LOGIN VARCHAR(255),	
	HostName VARCHAR(255),	
	BlkBy VARCHAR(255),	
	DBName VARCHAR(255),	
	Command VARCHAR(MAX),	
	CPUTime BIGINT,	
	DiskIO BIGINT,	
	LastBatch VARCHAR(255),	
	ProgramName VARCHAR(255),	
	SPID INT,	
	REQUESTID INT
)
INSERT INTO @tbl
EXEC sp_who2
--	--------------------------------------------
SELECT *
FROM @tbl
WHERE STATUS <> 'BACKGROUND'
	AND STATUS <> 'sleeping'
ORDER BY CPUTime DESC, DiskIO DESC

Open in new window

"Background" statuses are internal SQL processes and "sleeping" connections aren't being used right now - they're minimal overhead, probably less than constantly closing/reopening them.

bominthu

ASKER

Hi grendel777

From your result, how would I know from which computer the connection is established ?
I need to know from which computer heavy query is established.

Hi pcelba
What does SQL profiler do ? I heard about people use it but I've never used it.
Can brief me how to use profiler and what function of its help ? What does indexing do ?
I have done database mirroring before but Active and Passive.
You mean to do Active and Active mirroring ? Can be done ? It is a good idea but what would be the impact to production database(current active DB) if I do Active Active mirroring ?
lets say I do mirroring, I still need to find out who are the user using which computer running heavy query from excel because I just start supporting this customer and I don't have complete info which are the user running query using excel. I know two users but I need to know exactly to configure their PC to query data from mirrored DB. Without knowing exactly which computer running excel query, mirroring won't help.

Thanks

grendel777

The "hostname" is the connecting computer, and the "login" is who's logged into it. It's most useful if the client machines are well named so you really know which machine is which.

SOLUTION

Pavel Celba

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bominthu

ASKER

Hi Grendel

Your command seems to be useful. I can see hosts connected and Program name but most are SAP business and Icrystal SAP and Microsoft SQL server. If the query ran from user PC using Excel, would it show as Microsoft Excel ?

There are also CPU time, Disk0, Program name , SPID . What are those referring to ?

I also see some results without showing host name, what are those coming from ?
For Login all showing sa. Does that mean everyone connected using sa ? SAP log in IDs are not from SQL ?

Thanks
Untitled.png

Qlemo

Hostname is provided by the connecting client, so can be empty. Same for OS info.
SAP connects with a single account. A server is handling all actions, not the client, so only generic info is available.
Anything else than SAP should be visible as such, including Excel.

bominthu

ASKER

What does CPU time and DISKIO referring to ?

Possible to find which connection is consuming the amount of MEMORY in server ?

Thanks

bominthu

ASKER

Hi Pcelba

I just read about mirroring that "The mirror database is restored from the principal with no recovery leaving the database inaccessible to the end users"
Does that mean mirrored database inaccessible as live db ?

Qlemo

Of course it is inaccessible. Mirroring is not replication, but keeping a up-to-date copy on another server in case of failure. What would happen if someone uses the mirror db instead of the live db? Since MS SQL uses time markers even without anyone changing the db, there would be no way to keep the DBs in sync anymore.

Qlemo

Regarding memory consumption: Assigning that to a session isn't easy. Data can and should be shared between sessions.

bominthu

ASKER

Thanks for your responds.

So by running sp_who2 based on High CPU usage, High IO usage, Multiple entries for the same SPI, I can conclude those are the PCs running heavy reports ?

I have done SQL database replication before for backup DB purpose. I encountered once replications works for one of my customers but once didn't work also for another customer with some errors I didn't know how to fix.

Let's say I managed to identify who are the users running heavy reports, will replication help for my situation ? Let's say I'll connect some of the user SAP client/excel users to replicated DB. Would that help for my situation ? What would be the impact/advantages and disadvantages ?

Thanks

Qlemo

It is important to find the culprit, then decide depending on its behaviour and relevance. It might be better to have a report DB somewhere else, which is R/O and has cummulated and prepared information updated on schedule, for example.

"Replication" does not help. What you search for as generic solution is Load Balancing, which includes ad-hoc decisions which server to use. That is something really complex and not to be triffled with.

Muhammad Burhan

for question, please consider the link for additional query to get RAM utilization by query or queries.
http://sql-kevin.blogspot.com/2010/08/sql-server-2008-query-memory-usage.html

While issuing that query it will wait until some query is running and will collect the memory usage used by queries.

bominthu

ASKER

I run sp_who2 recently when users complain about SAP hang and identify some workstations that shows with high I/O and non of them are showing Excel user.

I have also asked user if they have ran such report and they say they didn't run any excel report.

But whenever SAP hang, restarting SQL fix the issue.

I really can't think what was causing it.

Any suggestion ?

Thanks

SOLUTION

Qlemo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bominthu

ASKER

Hi Qlemo

When you says ""hang" is a very broad description. It is unlikely a single query stops the DB from responding. A lock might.." I think it is right.

Recently whenever user complain SAP hang, I see which host consuming more CPU and memory and restart those machines but it didn't help.

Finally I had to restart SQL service only.

So what kind of lock you are referring to ? I feel like something is locking and not unlocking when it is hang.

Could you advise further to find out culprit ?

Thanks

Tony Giangreco

I would run queries against the logs to try and nail down specifics on the problem. Since you have been working on it for a while with no resolution, running a periodic query on the logs may provide insight into the user or process that's causing the lock or hang situation.

Here is a site with info that may help:
dba.stackexchange.com/questions/54494/sql-server-keep-track-of-all-connections-disconnections

Hope this helps!

Qlemo

I already told you a simple approach in http:#a41318487. The more complex one, but with history, is using the SQL profiler with filters like "query running more than x seconds". Logging lock cancellation gives an indication for excessive luck waits.