Wednesday, July 3, 2013

[MS SQL Server] Procedure Cache Size: Monitoring and Adjusting

[MS SQL Server] Procedure Cache Size: Monitoring and Adjusting

Procedure Cache Size: Monitoring and Adjusting

Posted: 03 Jul 2013 04:45 AM PDT

So, I'm looking into ways to increase procedure cache size, and I was wondering if someone could help me figure out how to monitor it, and how to change it. Can I get some help to start on this issue?

Overhead on a NC index if the key fields are not updated during production.

Posted: 02 Jul 2013 09:32 AM PDT

My next question while I am in the INDEX zone.As an example:I have a large table (4 - 5 million rows) and I have say five NC indexes on the fields being used in WHERE and JOIN clauses for the incoming queries.If their is never [or minimal] UPDATES on any of the fields in the NC clustered indexes (INCLUDE fields as well) then is it fair to say that their would only be minimal operational overhead related to updating the NC indexes? i.e UPDATES will happen very frequently to the data in the table but only on non NC indexed fields so only the Clustered index would need to be updated?I guess their will be other overhead of keeping these indexes INSERTS, query plan compilation, memory used etc?Obviously maintenance will need to be performed but I am talking about production hours activity.thanks

Right click on a Database, select properties Error, cannot show requested dialog.

Posted: 02 Jul 2013 11:54 PM PDT

I right click on a Database, select properties Error, cannot show requested dialog.The error reads:cannot show requested dialog (SqlMgmt)Property Size is not available for Database '[DataWarehouse]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SQLServer.Smo)I'm running Database Consistency Checker.Has anyone experienced this problem?

Restore - Exclusive access could not be obtained because database is in use.

Posted: 02 Jul 2013 11:11 PM PDT

Even with setting the database to SINGLE_USER WITH ROLLBACK IMMEDIATE (on the line immediately prior to the restore command), I am getting these errors occasionally on nightly restore:Exclusive access could not be obtained because the database is in use. [SQLSTATE 42000] (Error 3101) RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). Exact code is below. Really can't wrap my head around this, if it got to restore database command, then the single user command must've been successful, right? Could something else steal the single connection away from me while this is running?[code="sql"]ALTER DATABASE DbName SET SINGLE_USER WITH ROLLBACK IMMEDIATE; RESTORE DATABASE [DbName] FROM DISK = N'Z:\DbName.bak' WITH FILE = 1, MOVE N'DbName' TO N'D:\SqlData\DbName.mdf', MOVE N'DbName_log' TO N'D:\SqlData\DbName.ldf', NOUNLOAD, REPLACE, STATS = 10GOALTER DATABASE DbName SET RECOVERY SIMPLE;ALTER DATABASE DbName SET MULTI_USER;GO[/code]

need to delete the datbase and then restore of the different backup on daily bases

Posted: 02 Jul 2013 11:40 PM PDT

Good Morning I would appreciate if I can get a syntax to run, this is what I need to accomplish,Delete the database, (it gives me error with connections open)Once it is deleted I want to restore it of .bak file,Help is much appreciated,Regards,Bubby

Finding unused fields

Posted: 02 Jul 2013 07:00 AM PDT

We have a db with 388 tables/5,950 fields. I was just asked if I could find any fields (in the entire freaking db) that have never had any data populated in them. YIKES! Is there a quick and simple way to do this?

Creating a DBA Administration Database

Posted: 02 Jul 2013 08:47 PM PDT

Good Morning / Afternoon / Evening (depending on where you are)I'm going to be setting up a DBA Admin database to replace an old outdated one that contained very minimal logging and would like the shiny new one to collect all the useful information that is available to us (at the moment in 2005 but will soon be 2012) around database performance and statistics. I've been searching quite a bit on this to get advice and best practice but either my Googlefu is failing or there is not much info out there on this.In terms of what I would like help with is some info on:- What is best practice- What tables should it contain- What scripts to run- What jobs to set-up- What tables should it contain- Anything not contained in the above that I have missed and would be helpful knowing :hehe:Any scripts and examples of databases ect would be welcomed :-)Thanks allAndy

Urgent Issue Reg Checkdb issue please respond

Posted: 02 Jul 2013 11:21 AM PDT

Hi guys ,I am trying to run checkdb in my prod its failing from last 2 months because of tempdb space issue , but on another server with same db size and tempdb size its executed successfully.my question the one which is failing that is not properly oragnized i mean no proper indexes but the other one has all relational properties do checkdb operates depending on how critical of database(i mean indexes and type of data)will it operate differently on heap and index tables

New NC Index or new INCLUDE on Existing Index

Posted: 02 Jul 2013 09:16 AM PDT

I have a query that does not run very frequently but when it does it table scans a large table so causes issues.This query has only one value in the WHERE clause and returns 21 fields.The value in the WHERE clause is not in any NC indexes.[b]Initial Execution:[/b]Clustered Index Table Scan:1878874 Reads 181902msI tested two scenarios.[b]1. [/b]Add a new NC index for the WHERE clause value. To many fields to cover so a bookmark look up will be inevitable.The query uses the new index and peforms a SEEK.7 Reads4ms total time151mb Index Size[b]2[/b]. Add an INCLUDE column to an existing NC Index containing the WHERE clause value (remove new NC index first)The query now performs a SCAN but on the newly created INCLUDE data.29647 Reads3277ms total time101mb additional size to NC indexSo it looks like adding a new NC index is the better option.As a rough guess would you say the small Index size increase and the overhead of maintaining all Index levels (rather than just the leaf for an INCLUDE field?) would mean adding a new NC index is the better of the two options?I could live with either option but if their is little difference then it would make sense to add a new NC index.As a side note: I ran the offending query through the DTA as work load file and as expected it came back with a new index as the recommendation but it also included all 20 fields in the INCLUDE list which I thought in this case was a little pointless.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog