December 2, 2010 at 11:53 am
Hi guys, I'm hoping you might be able to give me some tips about configuring our server. We have a really crappy server, so I want to make sure I'm setting it up the best possible way. First, I'll give you our server specs:
Windows 2003 R2 64 Bit, 2CPU, 8GB ram, Intel xeon CPU @ 2.93GHZ
I have attached an excel file that has our current sp_configure settings in it.
Specifically, my question would center around the memory settings and the remote querying settings...
I am seeing a lot of suspended processes and wait times associated with network i/o and memory. So, I just wanna make sure we are setting it up as best we can to deal with crappy servers.
All of our servers are virtual servers and we have our sql server then we have coldfusion apps that hit sql from a custom web interface and a cognos load balancing enviornment set up with 2 servers that hit the sql server as well.
Our users complain about long running reports and we try to nip them in the bud by adjusting indexes and stuff but just looking for any kind of advice that you guys might have. 🙂
Thanks!
December 2, 2010 at 12:08 pm
Is this a dedicated server for SQL Server? If yes then the max memory is set too low (3500 MB out of 8192 MB). I would leave 2 GB to the OS and set the max memory to 6 GB.
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
December 2, 2010 at 12:56 pm
Yes, it is dedicated to SQL. When we had first set the server up we had some crazy performance issues because I didn't have any value set in there, so I set it 3.5 to be conservative... but I can jack it up to 6 and see what happens.
December 2, 2010 at 1:58 pm
Here are the ones I'm concerned about:
max degree of parallelism1
Are you sure to prevent parallel processing on the server level? Is the server mainly used for OLAP or OLTP processes?
Ole Automation Procedures1
Do you use any kind of OLE related commands (like sp_OAGetProperty)?
xp_cmdshell1
Finally, the most dangerous one... I strongly recommend to turn it OFF by default and enable it only when needed (if at all). This option will allow each and every user to get access to the file system and OS commands (depending on the resulting privileges).
Worst case scenario:
A stored procedure runs with EXECUTE AS and the owner it'll run as has admin privileges.
The sproc is not protected against SQL injection and will not use xp_cmdshell as part of the code (so you wouldn't consider this sproc being affected by the xp_cmdshell setting).
Effect: via SQL injection it is possible to create a Windows admin account. And you wouldn't even notice. Ouch.
December 2, 2010 at 2:42 pm
LutzM (12/2/2010)
Here are the ones I'm concerned about:max degree of parallelism1
Are you sure to prevent parallel processing on the server level? Is the server mainly used for OLAP or OLTP processes?
Ole Automation Procedures1
Do you use any kind of OLE related commands (like sp_OAGetProperty)?
xp_cmdshell1
Finally, the most dangerous one... I strongly recommend to turn it OFF by default and enable it only when needed (if at all). This option will allow each and every user to get access to the file system and OS commands (depending on the resulting privileges).
Worst case scenario:
A stored procedure runs with EXECUTE AS and the owner it'll run as has admin privileges.
The sproc is not protected against SQL injection and will not use xp_cmdshell as part of the code (so you wouldn't consider this sproc being affected by the xp_cmdshell setting).
Effect: via SQL injection it is possible to create a Windows admin account. And you wouldn't even notice. Ouch.
About the parallelism: I was told that I should set this setting less than the available CPUs that are on the server. As we only have 2, I set it to 1. Is that not accurate?
OLE: No, I do not think we are using any OLE related things... what do people usually set this as?
xp_cmdshell: I have this set to on on purpose. We specifically use xp_cmdshell in our procs to do a lot of stuff. I know the ramifications of it.
Our server is not a transactional mission critical system. Its just a reporting data mart that gets data feeds from other systems.
December 2, 2010 at 3:29 pm
amy26 (12/2/2010)
About the parallelism: I was told that I should set this setting less than the available CPUs that are on the server. As we only have 2, I set it to 1. Is that not accurate?OLE: No, I do not think we are using any OLE related things... what do people usually set this as?
xp_cmdshell: I have this set to on on purpose. We specifically use xp_cmdshell in our procs to do a lot of stuff. I know the ramifications of it.
Our server is not a transactional mission critical system. Its just a reporting data mart that gets data feeds from other systems.
Parallelism: If the server is dedicated to SQL only, then there's not really a valid reason to limit it to max-1. If you would have a well-tuned OLTP system, there would be very few scenarios where a query would benefit from parallel processing (decided by the query optimizer/analyzer). In an OLAP environment you typically would prefer parallelism since a query would benefit from additional resources. Therefore, I would set it to 0 to use all available sources and monitor CX_PACKET waits if there is a need for query tuning or a MAXDOP setting for a specific query. But I wouldn't limit the processing power by default...
OLE: There won't be a specific benefit or downside, as far as I'm concerned. It's more because of "If I don't use it, it shouldn't be enabled."
xp_cmdshell: I see your point. But I still recommend to turn it OFF in gernal and turn it ON if needed. (Again, not a performance issue, but a security issue).
December 2, 2010 at 3:37 pm
Cool thanks some good advice.
So, I've changed my memory usage to 6Gigs
I've changed the paralellism to 0
I just kicked off a query that is pretty simplistic:
select top 10 * from table where accounting_pd is not null
now, this table has like 300 million records in it mind you... but its partitioned and has lots of indexes.
In the process monitor, that session is "suspended" and its in a "PAGEIOLATCH_SH" wait type right now ... to me, that doesn't sound right. Query has been running for almost 5 minutes now.
December 2, 2010 at 3:49 pm
amy26 (12/2/2010)
Cool thanks some good advice. So, setting the parallelism to zero means it won't be using any parallelism right?
Nope. The opposite.
Curently, you have it set to 1, meaning NO parallelism. Each query will only use one processor. Oddly enough, it will still use both processor on an overall picture, but not based on a single query. When changing it to 0 it will allow SQL Server to use all processors available to split the load between all processor availabe. (The max memory is controlled separately.)
Set it to 1: less processing power (= probably a few queries will take longer than without parallel processing).
Set it to 0: parallel processing will most probably speed up some heavy queries. But you should monitor CX_PACKET waits to see if there is a need for query improvement or a specific MAXDOP(1) for this query.
December 2, 2010 at 3:56 pm
Cool thanks again, I actually edited my post after I just used google to look up the answer to my question! 🙂 I didn't realize we had actually turned it off...
But, to the query I kicked off (and decribed in my post above) its still running...15 minutes now...and it is still suspended and running with a wait type of PAGEIOLATCH_SH
December 3, 2010 at 3:20 pm
amy26 (12/2/2010)
Cool thanks again, I actually edited my post after I just used google to look up the answer to my question! 🙂 I didn't realize we had actually turned it off...But, to the query I kicked off (and decribed in my post above) its still running...15 minutes now...and it is still suspended and running with a wait type of PAGEIOLATCH_SH
The PAGEIOLATCH_SH is caused either by disc I/O issues, mostly due to large table scans.
So it seems like your query performs a table scan due to the lack of a supporting index or out of date statistics or badly fragmented indexes.
Would it be possible to post the complete table def (including definition of all indexes) and the actual execution plan of your query? Also, please include the statistics information for each index (DBCC SHOW_STATISTICS ('table',index_name) excluding the last output showing RANGE_HIGH_KEY) as well as fragmentation level per index using sys.dm_db_index_physical_stats in 'LIMITED' mode.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply