June 4, 2014 at 8:06 pm
I'm a newbie DBA and am trying to collect statistics for our existing SQL Server 2008 database in preparation for an upgrade to SQL Server 2012/Windows Server 2012 later this year. Our largest, mission-critical database will get its own server on brand new hardware, and it sounds like the database performance on the server itself will not be a concern, but I don't know if I've found all the answers re: network throughput needs for the database. Sorry if this is an obvious question, but I've been Googling and searching SSC all afternoon without finding what I'd recognize as the solution!
Our network administrators have helped gather server performance metrics from their monitors, and I've been reading up on SQL Server metrics available in the DMV tables. Between those two approaches, I think we've got a lot of data about the Business Objects Enterprise (Crystal Reports server) and database processes controlled by the IS department.
But I feel like there's another data stream using direct network bandwidth and I'm stuck about how to measure its capacity needs. For this other pipeline into SQL Server, the business intelligence department (outside of IS) creates solutions for their users in Excel, using OLEDB (or maybe even ODBC) connections, VBA and custom stored procedures to drive these workbooks. I haven't found a way to directly capture or measure this activity. But I know the users run these on demand from their local machines (no Sharepoint or SSRS), the number of these solutions keeps growing, and the IS team is not privy to the run frequencies or schedules. That doesn't give me warm fuzzies at all but that is the current state of affairs. :ermm:
So, are there performance counters in SQL Server or Windows Server that would give an idea how much network or server bandwidth these connections use so the new server is provisioned appropriately? I saw this Confio article on OLEDB Wait Types (http://www.confio.com/logicalread/sql-server-oledb-wait-type/#.U4-nwii-SKs) but it appears to pertain to SQL Server calls *out* to OLEDB, not Excel connections bound to SQL Server. In the long run, transitioning this group to SSAS and SSRS might be a way to bring their data needs back through IS channels, but that discussion is not even on the distant horizon at this point.
Thanks for any suggestions!
Becky
June 5, 2014 at 12:28 am
The OLEDB wait type is just saying that SQL is waiting on activity occuring outside SQL server which is connected via OLEDB.
The activity is likely caused by some consumer or VBA script inside a spreadsheet that runs locally on the users machine. It's not really anything to be concerned about at the SQL level, other than when it blocks other queries.
With a dedicated server, network throughput is highly unlikely to be a concern as most servers are connected via 10Gb ethernet. You're going to see IO and CPU problems well before network saturation. A simple perfmon capture can show you that.
You could however measure the IO and CPU usage of the queries that the spreadsheet performs. Either run a SQL trace or create an extended event to trace the number of logical reads and writes for the queries for the hosts/logins which run the spreadsheets.
If you are upgrading, you'd expect to see similar logical IO and possibly less total overall CPU usage as a percentage. If you're saturating the network connection then something else is wrong.
June 5, 2014 at 12:45 am
Talk to your BI department and try to convince them to build an actual solution worthy of the name BI. 😉
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 5, 2014 at 4:34 am
Regardless of the source, those are still just queries coming into the system. If you're capturing query metrics using any of the common approaches such as querying the cache through the DMVs, a server-side trace, or extended events, you should be capturing those queries as well.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 5, 2014 at 4:46 pm
Thank you all for the information! That gives me more confidence in the measures I have collected.
And moving/leading the team toward a more refined form of BI is a longer term aspiration 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply