writes from a select query? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

writes from a select query?

Author

Topic

influent
Constraint Violating Yak Guru

367 Posts

Posted - 2011-11-14 : 17:26:54

If the number of writes for a SELECT query is greater than zero and the query doesn't use temp tables, does that mean the query is doing something else in tempdb?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-15 : 00:03:30

it can even be internal temporary table sql server uses for sorting,hash matching etc for example in case where you use ranking/window functions it does sorting internally

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Sachin.Nand

2937 Posts

Posted - 2011-11-15 : 01:34:53

quote:
Originally posted by visakh16

it can even be internal temporary table sql server uses for sorting,hash matching etc for example in case where you use ranking/window functions it does sorting internally

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Not necessary if there is enough memory for the above operations.

PBUH

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-15 : 03:14:27

quote:
Originally posted by Sachin.Nand

quote:
Originally posted by visakh16

it can even be internal temporary table sql server uses for sorting,hash matching etc for example in case where you use ranking/window functions it does sorting internally

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Not necessary if there is enough memory for the above operations.

PBUH

Ok Thats fine

I was just telling a case when it can occur

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-15 : 03:21:00

see this for cases where it uses internal objects

http://msdn.microsoft.com/en-us/library/ms345368.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

influent
Constraint Violating Yak Guru

367 Posts

Posted - 2011-11-16 : 14:31:54

Should I be able to tell from these stats if my server is under memory pressure and writing to the pagefile on disk? My MEMORY:Pages/sec counter is typically very low (under 100) and my "Paging file: % Usage" counter hovers around 3.

----------------------------------------------------------------------------------------------------
Memory usage details for SQL Server instance PRODSERVER(10.50.1600.1 - X64) - Enterprise Edition (64-bit))
----------------------------------------------------------------------------------------------------

--------------------------------------------------------------
Memory Configuration on the Server visible to Operating System

Physical Memory_MB Physical Memory_GB Virtual Memory MB
--------------------------------------- --------------------------------------- ---------------------------------------
65525.734375000 63.989974975585 8388607.875000000

-------------------------------
Buffer Pool Usage at the Moment

BPool_Committed_MB BPool_Commit_Tgt_MB BPool_Visible_MB
--------------------------------------- --------------------------------------- ---------------------------------------
58424.593750 58424.593750 58424.593750

-------------------------------------------------------
Total Memory used by SQL Server instance from Perf Mon

Mem_KB Mem_MB Mem_GB
-------------------- --------------------------------------- ---------------------------------------
59826784 58424.593750 57.055267333

-------------------------------------------------------------
Memory needed as per current Workload for SQL Server instance

Mem_KB Mem_MB Mem_GB
-------------------- --------------------------------------- ---------------------------------------
59826784 58424.593750 57.055267333

------------------------------------------------------------------------------
Total amount of dynamic memory the server is using for maintaining connections

Mem_KB Mem_MB Mem_GB
-------------------- --------------------------------------- ---------------------------------------
6280 6.132812 0.005989074

------------------------------------------------------------
Total amount of dynamic memory the server is using for locks

Mem_KB Mem_MB Mem_GB
-------------------- --------------------------------------- ---------------------------------------
728160 711.093750 0.694427490

----------------------------------------------------------------------------
Total amount of dynamic memory the server is using for the dynamic SQL cache

Mem_KB Mem_MB Mem_GB
-------------------- --------------------------------------- ---------------------------------------
374136 365.367187 0.356803894

-------------------------------------------------------------------------
Total amount of dynamic memory the server is using for query optimization

Mem_KB Mem_MB Mem_GB
-------------------- --------------------------------------- ---------------------------------------
1084432 1059.015625 1.034194946

-------------------------------------------------------------------------------
Total amount of dynamic memory used for hash, sort and create index operations.

Mem_KB Mem_MB Mem_GB
-------------------- --------------------------------------- ---------------------------------------
1059792 1034.953125 1.010696411

------------------------------------------
Total Amount of memory consumed by cursors

Mem_KB Mem_MB Mem_GB
-------------------- --------------------------------------- ---------------------------------------
4728 4.617187 0.004508972

-------------------------------------------------------------------------
Number of pages in the buffer pool (includes database, free, and stolen).

8KB_Pages Pages_in_KB Pages_in_MB
-------------------- --------------------------------------- ---------------------------------------
7478348 59826784.000000 58424.593750000

---------------------------------------
Number of Data pages in the buffer pool

8KB_Pages Pages_in_KB Pages_in_MB
-------------------- --------------------------------------- ---------------------------------------
5175688 41405504.000000 40435.062500000

---------------------------------------
Number of Free pages in the buffer pool

8KB_Pages Pages_in_KB Pages_in_MB
-------------------- --------------------------------------- ---------------------------------------
1078756 8630048.000000 8427.781250000

-------------------------------------------
Number of Reserved pages in the buffer pool

8KB_Pages Pages_in_KB Pages_in_MB
-------------------- --------------------------------------- ---------------------------------------
123383 987064.000000 963.929687500

-----------------------------------------
Number of Stolen pages in the buffer pool

8KB_Pages Pages_in_KB Pages_in_MB
-------------------- --------------------------------------- ---------------------------------------
1223904 9791232.000000 9561.750000000

---------------------------------------------
Number of Plan Cache pages in the buffer pool

8KB_Pages Pages_in_KB Pages_in_MB
-------------------- --------------------------------------- ---------------------------------------
968703 7749624.000000 7567.992187500

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources