February 27, 2012 at 8:57 am
+1
GilaMonster (2/27/2012)
False.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
February 28, 2012 at 10:36 am
wclemmer (2/27/2012)
Just a quick poll(ish) question. [No pun intended.]True or False: If the SQL Server service is the only thing running (dedicated server), the default max memory setting of (bignum) is actually preferred as SQL Server is intelligent enough to handle its own memory management automatically.
You should ALWAYS set the max memory for EVERY SQL Server instance in existence. That is one of the few things where "it depends" doesn't apply. 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 28, 2012 at 5:39 pm
Did not realise that this topic was still going. Well I have something to report. The client has finally appointed a DBA and I shall gladly handover everything I did and move back to my much loved software development. I did find another issue with the SQL Server setup though. The disks that it uses are all on same physical SAN device which happens to be the same device entire company uses for pretty much everything. They said but all physical files are on different drives which are in different LUNs and my & my boss's argument with that was, but is still basically a same physical device. We think that there can be a big improvement with a better disk stategy.
WRACK
CodeLake
March 29, 2012 at 9:09 am
WRACK (3/28/2012)
Did not realise that this topic was still going. Well I have something to report. The client has finally appointed a DBA and I shall gladly handover everything I did and move back to my much loved software development. I did find another issue with the SQL Server setup though. The disks that it uses are all on same physical SAN device which happens to be the same device entire company uses for pretty much everything. They said but all physical files are on different drives which are in different LUNs and my & my boss's argument with that was, but is still basically a same physical device. We think that there can be a big improvement with a better disk stategy.
Perform a file IO stall analysis during various periods while things of concern are running.
This query will get you cumulative stats since sql server start time:
SELECT DB_NAME(fs.database_id) AS [Database Name], mf.physical_name, io_stall_read_ms, num_of_reads,
CAST(io_stall_read_ms/(1.0 + num_of_reads) AS NUMERIC(10,1)) AS [avg_read_stall_ms],io_stall_write_ms,
num_of_writes,CAST(io_stall_write_ms/(1.0+num_of_writes) AS NUMERIC(10,1)) AS [avg_write_stall_ms],
io_stall_read_ms + io_stall_write_ms AS [io_stalls], num_of_reads + num_of_writes AS [total_io],
CAST((io_stall_read_ms + io_stall_write_ms)/(1.0 + num_of_reads + num_of_writes) AS NUMERIC(10,1))
AS [avg_io_stall_ms]
FROM sys.dm_io_virtual_file_stats(null,null) AS fs
INNER JOIN sys.master_files AS mf
ON fs.database_id = mf.database_id
AND fs.[file_id] = mf.[file_id]
ORDER BY avg_io_stall_ms DESC OPTION (RECOMPILE);
I would also recommend a waitstats analysis too.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 5 posts - 31 through 34 (of 34 total)
You must be logged in to reply to this topic. Login to reply