SQL Server (Page file issue , Defragmentation , table property & tempdb issue)

Question

Recently i hv attended one interview he asked two questions for those i was unable to satisfied him.

1.Page file issue (suppose page file use 90%) then what need to be done from DBA end ?

2. One table is there with clustered index (int or varchar data type) and we hv done rebuild but when we check fragmentation level its not going down. why ?

3. Can we chnage the property (Alter command..) of table (it is part of Replication) without removing from articles ?

4. Tempdb is grown to 100GB we verified 1.) there is no open tran 2.) performed DBCC FREEPROCCACHE then shrunk tempdb after that we checked the size of tempdb that was same (100GB). plus we can't restart SQL Service. in this scenario what need to be done from DBA end for reclaming the disk space.

  

---

india

Answer 1

Hi,

1. If the OS is using the page file and therefore paging data I would start to consider lowering or setting the max memory setting for the instance. If the OS runs low of memory it will force SQL to start dumping pages out of the buffer cache to disk and if the OS is low on memory it may be forced to start using the page file. Both of these are not good for performance however I would really need to do some digging into the current config before being sue.

2. How many pages make up the clustered table? In small tables such as less than 1000 you will often find that fragmentation will stay high due to the small amount of pages being used SQL may have allocated only a handful of pages and therefore the clustered table may be using mixed extents and therefore it can't reduce the fragmentation. You can disable mixed extents in the instance via a trace flag if you need to. If the clustered table is large the workload or schema config may be re-introducing fragmenetation by the time you check the fragmentation again.

3. Yes. I believe you can except using the SWITCH statement for partitioned tables is not allowed for replicated tables.

4. Are you using snapshot isolation? If so the version store may be causing tempdb to grow as the version store is stored in tempdb. If you are using snapshot isolation you are in a sticky situation as you can't shrink tempdb or shrinkfile tempdb whilst any operation is using tempdb. Again more information is required.

---

Sean Massey | Consultant, iUNITE

Feel free to contact me through My Blog, Twitter or Hire Me.
Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

Answer 2

As Sean mentioned for 4) if there are some activities on the server,temdb you cannot reclaim the disk space but only restart the MS SQL server.

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 3

Hi Sean,

I got answer of my question no 2 & 3. for question no 4 there is no activity is running plus we dont use any kind of REplication but it does not allow to reclaim the disk space unless until we do not start sql services.

for question 1. we got one ticket and verified Memory was OK (used 50% memory what we configured for SQL). and checked peg file (Computer-> properties->Advance->performance setting->advance->virtual memory (C:\) )

Recommended was 50GB but we gave only 10GB so if we increase this value. does it make any improvement at SQL Server level in page file issue ?

---

india

Answer 4

What message do you receive when you try to shrink? Did you check your snapshot isolation state?

USE master
go
SELECT name, snapshot_isolation_state_desc from sys.databases order by name

With regards to page file size - no increasing the page file size will NOT help SQL performance. If your OS is using the page file then SQL is going to suffer. How much memory is in the system and how much is the max memory setting for the SQL instances? You don't want a SQL server to be using the page file.

---

Sean Massey | Consultant, iUNITE

Feel free to contact me through My Blog, Twitter or Hire Me.
Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

Answer 5

snapshot isolation: showing off except master and msdb.

page file size: we used 32GB for Windows and 30GB for SQL.

plus i checked SQL Server:Buffer Manager:Page life expectancy is going 45000+

---

india

Answer 6

Yeah you can't disable master and msdb snap shot isolation. It does not sound like you are under memory pressure.

Can you run this query and see what is using the tempdb space?

SELECT SUM (user_object_reserved_page_count) *8 AS usr_obj_kb ,
SUM (internal_object_reserved_page_count)*8 AS internal_obj_kb ,
SUM (version_store_reserved_page_count) *8 AS version_store_kb,
SUM (unallocated_extent_page_count) *8 AS freespace_kb ,
SUM (mixed_extent_page_count) * 8 AS mixedextent_kb
FROM sys.dm_db_file_space_usage
-- usr_obj_kb = user temp tables/table variables
-- version_strore_reserved_page_count active trans with snapshot isolation

---

Sean Massey | Consultant, iUNITE

Feel free to contact me through My Blog, Twitter or Hire Me.
Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

Answer 7

usr_obj_kb           internal_obj_kb      version_store_kb     freespace_kb         mixedextent_kb

-------------------- -------------------- -------------------- -------------------- --------------------

34112                512                  0                    1496256              107520

(1 row(s) affected)

i think there is some proble which realtes to Page File. can you advice abt page file

---

india