please comfirm.

Jul 3, 2012 at 7:28 AM

Dear,

Should I use production database server this tools? any license need to update for monitoring SQL database server? I am facing error when connect remote server,  please tell me how to resolve this issues.

Failed to connect [ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified]

My PC - Windows 7 with SP1, installed admin pack also...

regards

ananda

 

Coordinator
Jul 3, 2012 at 8:41 AM

Yes you can this on a production system, many other users do.

I'm wondering if you have the correct SQL Native Client protocol installed on your PC.  Depending on the version of SQL Server you are trying to remote connect to, you would need to have the correct SNAC libraries present as well.  You can get these from the SQL installation media, or the Microsoft web site. 

Can you verify you have the SQL Native Client library for your version installed ?  Also verify that you are selecting the correct version in the Options -> Driver menu.

Jul 4, 2012 at 6:37 AM

Hi glensmall,

Thank you for reply, it is working after installed SQL 2008 client in my PC.

1. Plan cache Hit ratio - 40% to 60% over 1 hours monitoring at production database, this is cause of procedure cache plan not refersh with Ad hoc, prepared plan, SQL Statement etc.. please tell me how to increase plan cache hit ratio in production database.

2. Full scan/sec - variations 0 to 14 sec ( staying data pages at buffer pool), does this secondes are good? how to relove this issues and minimize Full scan/sec?

Thanks

ananda

 

Coordinator
Jul 4, 2012 at 9:11 AM

For the plan cache, it sounds like you have a lot of Adhoc queries being run or other queries that don't have plans that are already existing I the plan cache.  It may also be as a result of a lot of recompiles happening on your system.  You need to look at the plan cache (sql monitor have a link to let you do this), and you will be able to see if you have a lot of single use plans in there.  Also look to see if you are getting a lot of recompiles.

Since it's likely that you are running AdHoc queries, then you cold turn on "Optimize for AdHoc workloads" as a SQL Setting.  This would reduce the amount of wasted plan cache you have by the server only storing a plan stub rather than the full plan.  You probably want to have a talk with your developers and get them to start using paramerized stored procedures, or dynamic SQL - something that will force result of a plan rather than generating multiple ones all the time.  Stored procs are not always the answer to your solution, so you need to get an understanding of what queries are being run on your system, where they are coming from, and which ones would / should be converted from AdHoc into SP's or Dynamic SQL.  I would suggest reading Kimbery Tripps blog on SQLSkills.com as she discusses procedural code optimisation and plan cache usage in great detail.

 

Full Scans/sec- this means that you are either missing indexes on your tables, or you are not using the indexes properly, so they are generating scans.  I would suggest looking at the missing index DMV to determine if additional indexes could / should be created, and also look ay the index usage stats DMV to see which indexes are getting lots of scans, so that you can potentially modify them.

Another option you have for indexing if you don't understand indexing strategy very well, is to use the Database Tuning Advisor.  Simply use SQL Trace or profiler to capture a tuning trace, and let DTA read through it and make recommendations on indexes for your databases.  DTA is better in some cases that the missing index DMV, since DTA can go outside the box and think up different strategies, where as the missing index DMV simply tells you what indexes would help the existing plan.

Also, make sure you are doing regular index maintenance, and that you are also maintaining your DB statistics, since stats plan the most important part in query plan generation, which will help with your plan cache hit ratio.