Category Archives: Configurations

Setting up the Min and Max Memory options on the fly – Word of caution !

Posted on by

Apparently,setting up the Min and Max Memory setting for SQLServer should be definitely encouraged and no server should be left out with the default settings.But here is a quick word of caution around this –

Do you want to change these numbers on the fly randomly when there is good amount of load on the production system ?

The answer is ‘No’. You shouldn’t be changing these numbers on the fly for your random testing on a production box with good amount of load.

Changing these numbers using sp_configure and hardening the changes using a reconfigure or doing it via GUI will clear the plan cache, and that’s not something which you would like to happen on a busy production box.

Let me show a quick demo to explain this.

Step 1

I will query the employee table which is available for the AdventureWorks2012 database

USE [AdventureWorks2012]
GO
SELECT * FROM [HumanResources].[Employee]

Step 2 

The above query will cause an adhoc plan to be stored in the cache. We can verify this by querying the plan cache

SELECT st.text , qp.query_plan , cp.cacheobjtype , 
cp.objtype , cp.plan_handle 
FROM sys.dm_exec_cached_plans cp 
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st 
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp 
WHERE objtype = 'Adhoc';

Min Max Cache Relation1

Step 3

I will change the Min Server Memory of the server using sp_configure

sp_configure 'Min Server Memory (MB)',501
RECONFIGURE

As soon as I run this command,I can check the sql server error log to see messages like

SQL Server has encountered 1 occurrence(s) of cachestore flush for the ‘Object Plans’ cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
SQL Server has encountered 1 occurrence(s) of cachestore flush for the ‘SQL Plans’ cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
SQL Server has encountered 1 occurrence(s) of cachestore flush for the ‘Bound Trees’ cachestore (part of plan cache) due to some database maintenance or reconfigure operations.

Step 4

Query the plan cache again to see that its cleared

Min Max Cache Relation2

 

Conclusion

Changing instance level settings needs to done with care and if possible these should be done during a proper change/maintenance window for production systems.

Thanks for reading and keep watching this space for more.

Linked Server created using a script is missing Product name and Data source fields !

Posted on by

Today I will discuss about an issue which was observed while creating a linked server via script.

The existing linked server was scripted out from a SQL2005 instance and the same script was used to create a linked server under a SQL2012(SP1/CU2) instance.

This a common procedure to follow if you are doing a side by side upgrade of SQLServer and ensure you have all objects in place under the new server.

After running the script the linked server was created successfully, however important details like Product name, Data source fields were found missing.

Here is the view which will explain the problem –

Linked Sever Issue Missing

The newly created linked server will work just fine, and users wont be facing any issues other than the missing details.

This looked like a bug to me right away and I opened a case with Microsoft.

Connect Item details – https://connect.microsoft.com/SQLServer/feedback/details/782793/linked-server-created-using-a-script-is-missing-product-name-or-data-source-fields

This is not a critical bug which will impact anyone badly, so I have a strong feeling that Microsoft might set low priority for this item and a fix might come only during the next release.

Conclusion

This is not an issue which is major, however details like Product name, Data source available to view comes handy if you need to trouble shoot issues related to linked server.

Adding a Linked Server with ‘Oracle Provider for OLE DB’ as the provider – Proceed with caution !

Posted on by

Adding a linked server is an easy task which can be done at any point of time. I was under this same assumption until I came across a strange scenario.

CautionHere is the scenario for you all.

Use SSMS to create a New Linked Server and choose provider as ‘Oracle provider for OLE DB’

This one small step can bring the whole SQLServer to grinding halt if certain steps are not followed.

SSMS will be in frozen state during the New linked server creation step and eventually queries againt the databases will run forever.

Detailed explanation of the scenario

You wanted to create a new linked server with provider as Oracle provider for OLE DB.

Step 1 – The initial step to start of the whole process is to install the Oracle client on the SQLServer machine for which you want to add the linked server.

Step 2 – Oracle client installation will not force a reboot after the install is completed.

Step 3 – Without rebooting the server or recycling the SQLServices you will open SSMS and connect to the instance and see that the oracle provider is available.

LinkedServerIssue2

Step 4 – You will use SSMS wizard to create a new linked server and will choose the provider as Oracle provider for OLE DB.

LinkedServerIssue1

As soon as you select the provider you can notice that new linked server creation window will be in frozen state.

Any queries made against the databases will run indefinitely.

This situation will continue till you force a reboot of the machine. You cannot even recycle SQLServices as the service will fail to restart.

Think about a situation  –  You are doing this on a live production environment during normal business hours. As mentioned earlier this simple task can slowdown everything and can bring the server to a halt.In case the server is business critical, then rest is history(Start updating the resume !)

Right way of doing things

You will need to schedule changes like these ideally during a change window. You will need to recycle the SQL services or even bounce the box soon after the Oracle client is installed. This is a very important step. Once the server is up and running after the reboot you can either use SSMS to create the linked server or can opt to use sp_addlinkedserver system stored procedure. This time there wont be any slowness or performance related issues.

Conclusion

This simple scenario forced me to think twice and made up my mind to stick to one policy ‘Changes to business critical servers occurs only during a dedicated change window period’ no deviations what so ever.

Thanks for reading and keep watching this space for more.