how to identiyfy memory requirement to run the query

Question

how to identiyfy memory requirement to run the query

Megha P

SSCommitted

Points: 1976
More actions
December 10, 2014 at 12:00 am

#295821

We are transferring database to normalized structure,so there are several scripts for data transfer .
I have a script to transfer around 7 lakhs (7,00,000) records from One table to another table on different database on same server.
Currently on test server , i was testing and query shows in suspended states, i have tried with batch of 10,000 Records then also it is in suspended states.
SELECT physical_memory_in_bytes FROM sys.dm_os_sys_info;
Result is 8.48 GB --Physical memory
SELECT object_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Total Server Memory (KB)'
Result is 6 GB --Server memory
Now, How can i determine how much memory will be required to run the query ? query contains 7 to 8 columns ,it just simple selects from old table and insert to new table.
Thanks..

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

Neither of those values have anything to do with the query. The majority of that will be the SQL buffer pool, the plan cache and various other caches. They're not query-specific.

Look at the actual execution plan, look for the Memory Grant property. That'll be the workspace memory assigned to that particular query.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Megha P SSCommitted Points: 1976 More actions · Answer 2

ok..i will try to find out those counters..

Thank you GilaMonster

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 3

Not a perfmon counter. A property of the final operator in the query plan.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 4

And you can pull that plan from the cache, even while the query is running. It will be in sys.dm_exec_query_plan(). You'll need to pass it a plan handle. If the query is running, you can get it from sys.dm_exec_requests. If not, it'll be in sys.dm_exec_query_stats. An example to get you started:

SELECT *

FROM sys.dm_exec_requests AS der

CROSS APPLY sys.dm_exec_query_plan(der.plan_handle) AS deqp

--WHERE you'll need to supply a process id or something to help identify the query in question

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Megha P SSCommitted Points: 1976 More actions · Answer 5

Thanks..GilaMonster and Grant for guidance

i was able to get query plan from sys.dm_exec_query_plan using plan handle.However i have cancelled query as it was taking too long.

I found only CompileMemory Tag in Xml plan.Also don't found <MemoryFractions> tag.

Is there anything that will be generated for memory grant in xml execution plan ?

select * from sys.dm_exec_query_memory_grants

Above query result nothing while running query and after cancelling query also it returns zero record.

Megha P SSCommitted Points: 1976 More actions · Answer 6

Also i can see wait type PAGEIOLATCH_SH and wait time increasing ,while query is running.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

Can you post up the estimated plan for the query?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 8

Megha P (12/10/2014)
I found only CompileMemory Tag in Xml plan.Also don't found <MemoryFractions> tag.
Is there anything that will be generated for memory grant in xml execution plan ?

Could be the query didn't need a memory grant. Not all queries do.

Why are you specifically focused on the memory that this query uses? What's the larger problem here?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 9

Megha P (12/10/2014)
Also i can see wait type PAGEIOLATCH_SH and wait time increasing ,while query is running.

That's an indication of disk load. Not necessarily an indication of problems in memory (although it also doesn't automatically rule out memory issues). But the query was waiting on the disk.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Megha P SSCommitted Points: 1976 More actions · Answer 10

GilaMonster (12/10/2014)
Megha P (12/10/2014)
I found only CompileMemory Tag in Xml plan.Also don't found <MemoryFractions> tag.
Is there anything that will be generated for memory grant in xml execution plan ?
Could be the query didn't need a memory grant. Not all queries do.
Why are you specifically focused on the memory that this query uses? What's the larger problem here?

Whenever ,i run query it shows in suspended states for a while in activity monitor,then in running states afterwards again in suspended states..and so on.so i thought it can be memory issue.

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 11

Megha P (12/10/2014)
Whenever ,i run query it shows in suspended states for a while in activity monitor,then in running states afterwards again in suspended states..and so on.so i thought it can be memory issue.

Suspended means it's waiting for a resource. You need to identify what resource it's waiting for first, then investigate as appropriate.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 12

Run a query against sys.dm_os_wait_stats before and after running the query. It'll give you a pretty good idea what the query is waiting on.

Also, when you're running queries against sys.dm_exec_requests you will see the resource that a given query is waiting on. That's another clue to what the problem might be.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 13

Grant Fritchey (12/11/2014)
Also, when you're running queries against sys.dm_exec_requests you will see the resource that a given query is waiting on.

And sys.dm_os_waiting_tasks

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Megha P SSCommitted Points: 1976 More actions · Answer 14

Thank you Grant and Gail..

I have used dmv sys.dm_os_waiting_tasks and by using below query found that session_id which are in waiting tasks are of system process, as all records shows is_user_process = 0 .

so,there is no other process except system process in waiting

select is_user_process, * from sys.dm_exec_sessions S

INNER JOIN sys.dm_os_waiting_tasks S2 ON s.session_id = S2.session_id