The default setting is the wrong setting for SQL Server. However, unless this has caused you a problem or you are thorough to point of OCD, this may not be set on your server.
Unless you are fighting a memory bottleneck, it probably won’t affect you too much but it is hard to give SQL too much memory.
The setting is “Maximize Data Throughput for Network Applications” and on by default. It sounds like a good thing. To the contrary, here is documentation from MSDN.
http://msdn.microsoft.com/en-us/library/ms178067.aspx
Maximize Data Throughput for Network ApplicationsTo optimize system memory use for SQL Server, you should limit the amount of memory that is used by the system for file caching. To limit the file system cache, make sure that Maximize data throughput for file sharing is not selected. You can specify the smallest file system cache by selecting Minimize memory used or Balance.To check the current setting on your operating system1. Click Start, then click Control Panel, double-click Network Connections, and then double-click Local Area Connection.2. On the General tab, click Properties, select File and Printer Sharing Microsoft Networks, and then click Properties.3. If Maximize data throughput for network applications is selected, choose any other option, click OK, and then close the rest of the dialog boxes.
Maximize Data Throughput for Network Applications
To optimize system memory use for SQL Server, you should limit the amount of memory that is used by the system for file caching. To limit the file system cache, make sure that Maximize data throughput for file sharing is not selected. You can specify the smallest file system cache by selecting Minimize memory used or Balance.
To check the current setting on your operating system
1. Click Start, then click Control Panel, double-click Network Connections, and then double-click Local Area Connection.
2. On the General tab, click Properties, select File and Printer Sharing Microsoft Networks, and then click Properties.
3. If Maximize data throughput for network applications is selected, choose any other option, click OK, and then close the rest of the dialog boxes.
Happy Tweaking.
Here is a quick query I wrote today. It is the first time I had to go to this DMV so I thought I would share. It would be useful when planning for consolidation and troubleshooting a bunch of apps that have been consolidated or are hosted in a shared environment. Once you find the database, you can break it down by object and index with Tom Davidson's query.
select db_name(database_id) as dbName, count(*)*8/1024 as BufferPoolMB
from sys.dm_os_buffer_descriptors
group by db_name(database_id)
order by 2 desc
Here is query I relied on heavily while troubleshooting a customer with resource_semaphore_query_compile wait types. It is useful in other low memory conditions.select text, query_plan, requested_memory_kb, granted_memory_kb, used_memory_kb from sys.dm_exec_query_memory_grants MGCROSS APPLY sys.dm_exec_sql_text(sql_handle) tCROSS APPLY sys.dm_exec_query_plan(MG.plan_handle) High granted memory and used memory is what I looked at. Once you find the culprits, you can look through the plan for the usual suspects like hash joins. Just click on the xml link and save and a .sqlplan. Reopen it in SSMS.
In SQL 2000 I wanted to find unused indexes but found it to be difficult. I had to capture a huge trace that fully represents the workload and “trust” that the ITW knows what it’s talking about. You could also use the scan started trace event filtered by dbid, objectid, indexid. This procedure was very tedious.
In SS2005, a quick query of the dynamic management views lets you know which indexes are not being used. You can do this in a few minutes what had previously taken days or weeks. This alone is a very powerful feature.
I am proposing that you take it a step further. We are going to use a little logic and the missing index DMV’s to combine indexes and remove indexes that are still used but redundant. The steps in this process would look like this:
1. Remove unused indexes with the unused index script
2. Get your list of tables to analyze.
3. Remove redundant but used indexes.
4. Revue missing index DMV’s for mistaken index drops.
5. Combine indexes that where it is logical to do so.
6. Revue missing index DMV’s for mistaken index drops.
This methodology is most effective and viable when these conditions are met:
· The server has been online and thus collecting stats for a long time.
· The server is not pushing a hardware bottleneck. If so, this should be done during maint window.
· The server is enterprise edition and the tables allow online operations (i.e. no LOB data or partitions).
What are the benefits of removing unused indexes?
· Reduced writes during updates
· Reduced space usage
· Reduced backup\restore space and time
· Reduced index maintenance time
What are the benefits of removing unused indexes, used but redundant indexes and combining indexes?
· Less memory footprint for the same amount of data
· A different angle to index tuning
· Indexes are more likely to be covering
Now, let’s get down to business…
Remove Unused Indexes
This is pretty straightforward and relatively safe. You want to make sure that your server has been up long enough to get good index usage. This includes infrequent operations like month end reporting, etc.
--Unused indexes
declare @dbid int
select @dbid = db_id()
select object_name(s.object_id) as ObjName
, i.name as IndName
, i.index_id
, user_seeks + user_scans + user_lookups as reads
, user_updates as writes
, sum(p.rows) as rows
from sys.dm_db_index_usage_stats s join sys.indexes i on s.object_id = i.object_id and i.index_id = s.index_id
join sys.partitions p on s.object_id = p.object_id and p.index_id= s.index_id
where objectproperty(s.object_id,'IsUserTable') = 1 and s.index_id> 0 and s.database_id = @dbid
group by object_name(s.object_id), i.name, i.index_id, user_seeks+ user_scans + user_lookups, user_updates
order by reads, writes desc
You can most likely drop any indexes with zero or close to zero reads. The more rows, the more space you will reclaim. The more writes, the better write performance you get by removing them. If the index is not being read but writes are minimal and rows are low, there is little benefit of dropping it. Keep in mind that not having an index that you need is a lot worse than having an index you don’t need in most situations. I suggest starting off with a conservative approach.
Now that we have gotten rid of the low hanging fruit, we can get deeper. I get a list of tables by size and work my way down.
select object_name(object_id), max(rows)
from sys.partitions
group by object_name(object_id)
After running that query we see that tEvent is the largest table and should provide nice gains. This is where we start. Let’s use this schema for our example.
create table tEvent
(EventID int primary key clustered,
EventType int,
EventName varchar(100),
EventDetailID int,
CustomerID int,
CompanyID int,
DateOpen datetime,
DateClose datetime)
create index ind1 on tEvents(EventDetailID);
create index ind2 on tEvents(CustomerID);
create index ind3 on tEvents(EventDetailID, CustomerID, CompanyID);
create index ind4 on tEvents(CompanyID, DateClose, EventType);
create index ind5 on tEvents(CustomerID, DateClose, EventType);
Removing used but redundant indexes
If we do a sp_helpindex, we can see that ind3 should satisfy queries currently using ind1 and ind2. The keyword is “should”. It’s a fairly safe bet so we drop ind1 and ind2. Now we should monitor the missing index DMV to see if there is any negative impact. We should also see the read count substantially increase by using the unused index query filtered by tEvent. Here is the missing index monitoring query:
--Missing indexes
SELECT sys.objects.name
, (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) as Impact
, mid.equality_columns
, mid.inequality_columns
, mid.included_columns
FROM sys.dm_db_missing_index_group_stats AS migs
INNER JOIN sys.dm_db_missing_index_groups AS mig ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle
INNER JOIN sys.objects WITH (nolock) ON mid.object_id = sys.objects.object_id
WHERE (migs.group_handleIN
(
SELECT TOP (5000) group_handle
FROM sys.dm_db_missing_index_group_stats WITH (nolock)
ORDER BY (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) DESC)
)
and objectproperty(sys.objects.object_id, 'isusertable')=1and name = 'tEvent'
ORDER BY 2 DESC , 3 desc
As long as the indexes do not come back up in this report, we should be ok. Now we are servicing the same queries with 2 less indexes. Those are index pages that are no longer taking up buffer pool space!
Combining indexes
The same concept can be applied to the following scenario but a little more “feel” and understanding of how the app accesses the data is needed.
We know a former DBA added these. It is logical for us, based on our knowledge of the app and the cardinality of the data, to try to replace these indexes with this one.
create index ind6 on tEvents(CustomerID, CompanyID, DateClose, EventType) with (online=on, maxdop=8);
This index should satisfy all queries using both indexes. Again, we want go back to the missing index report to see if SQL thinks it needs one of those indexes.
Now work your way down your list of tables by row count. I was able to reduce the size of a 200GB database by 15% in addition to removing the unused indexes. That’s a big gain! The only problem I ran into was changing the name of an index that had a hint. There were a few indexes that I had to add back but since my approach was conservative it was nothing drastic and completely online.
Let me know if you have any other tips or questions regarding this topic.
I got well aquainted with this DMV when I was wrestling with resource_semaphore and resource_semaphore_query_compile wait types pre-sp2.
It looks like there are a few changes to it. Most notably, hooks for the resource govenor and ideal_memory_kb. BOL defines ideal_memory_kb as "Size, in kilobytes (KB), of the memory grant to fit everything into physical memory. This is based on the cardinality estimate." I think this will be very useful when troubleshooting plan/stats problems.
This looks like a great DMV that will give you a quick idea about what is on memory-wise.
The process_%_memory_low columns look sweet. Time to can dbcc memorystatus :)
SELECT [physical_memory_in_use_kb]
,[large_page_allocations_kb]
,[locked_page_allocations_kb]
,[total_virtual_address_space_kb]
,[virtual_address_space_reserved_kb]
,[virtual_address_space_committed_kb]
,[virtual_address_space_available_kb]
,[page_fault_count]
,[memory_utilization_percentage]
,[available_commit_limit_kb]
,[process_physical_memory_low]
,[process_virtual_memory_low]
FROM [master].[sys].[dm_os_process_memory]
And with dm_os_sys_memory we can troubleshoot without getting too deep into sys.dm_os_ring_buffers.
SELECT [total_physical_memory_kb],[available_physical_memory_kb],[total_page_file_kb],[available_page_file_kb],[system_cache_kb],[kernel_paged_pool_kb],[kernel_nonpaged_pool_kb],[system_high_memory_signal_state],[system_low_memory_signal_state],[system_memory_state_desc]FROM [master].[sys].[dm_os_sys_memory]
SELECT [total_physical_memory_kb]
,[available_physical_memory_kb]
,[total_page_file_kb]
,[available_page_file_kb]
,[system_cache_kb]
,[kernel_paged_pool_kb]
,[kernel_nonpaged_pool_kb]
,[system_high_memory_signal_state]
,[system_low_memory_signal_state]
,[system_memory_state_desc]
FROM [master].[sys].[dm_os_sys_memory]
Itzik Ben-Gan describes a method of analyzing trace files in his book "Inside Microsoft SQL Server 2005: T-SQL Querying" He provides a couple functions for stripping the literals from queries in the TextData column. If you do not use one of these functions or one of your own similar tools, you are not being very effective in your trace analyisis. The first one is from Microsoft PSS and can be downloaded here. The second one is a CLR function that uses regex. It performs much better. His book is worth it just for this function.
Here is the concept: Tuning single queries can be insane ineffective for multiple reasons. When facing a performance problem, you should be looking at query patterns that way to can get the biggest bang in the shortest amount of time.
Here is a simple trace analysis query using the PSS function.
-- Generate pattern and order by highest CPU
-- This is long running so you may want to dump it into a table.
--http://statisticsio.com/files/patterns/fn_tsqlsig.sql
SELECT dbo.fn_SQLSigTSQL(textdata, 4000)
, SUM(CPU)
, COUNT(*)
FROM dbo.trace_table
GROUP BY dbo.fn_SQLSigTSQL(textdata, 4000)
order by CPU DESC
Check out Itzak's book for more info on trace analysis. In this series on blog's, we will look at applying this concept to data in the DMV.