Monthly Archives: December 2010

FORCED PARAMETERIZATION IN SQL SERVER

 

 

One of the main benefits of using a stored procedure to execute Transact-SQL code is that once a stored procedure is compiled and executed the first time, the query plan is cached by SQL Server.

So the next time the same stored procedure is run (assuming the same connection parameters are used), SQL Server does not have to recompile the stored procedure again, instead reusing the query plan created during the first compilation of the stored procedure.

But, not all SQL Server-based applications use stored procedures to communicate with SQL Server.

Many times the Transact-SQL is sent from the application to SQL Server in the form of a static or dynamic statement.

Now SQL Server by default has the ability to do something called Simple Parameterization

Essentially, simple parameterization means that SQL Server can take a look at the static or dynamic Transact-SQL being sent to it from an application, and if it finds any values that it considers to be a parameter, it will parameterize the Transact-SQL, which allows the resulting query plan to be reused, much like how SQL Server can reuse the query plans of stored procedures.

For example consider the following SQL Query:-

SELECT fname, lname, address, city, state, zip FROM custinfo WHERE zip = ‘65742’

When SQL Server looks at this simple SELECT statement, it considers that ‘65742’ is a parameter, and that it is possible that another, very similar query will be run later, but with a different value for the parameter.

Because of this, when SQL Server compiles the query, it will parameterize & cache the query plan so that next time a similar query, will be able to re-use the cached query plan to execute it instead of having to recompile the statement each time it is to be executed.

For example, if the following two queries are run after the first one above, then SQL Server will be able to re-use the cached query plan, saving SQL Server resources and boosting overall performance.

SELECT fname, lname, address, city, state, zip FROM custinfo WHERE zip = ‘10005’

SELECT fname, lname, address, city, state, zip FROM custinfo WHERE zip = ‘99686’

But there is only one small problem. By default, only relatively simple queries can be parameterized.

If your application uses mostly simple queries, the default simple parameterization might be more than adequate to meet your performance expectations.

 

But, if application uses mostly complex queries, queries that cannot be automatically parameterized by SQL Server using simple parameterization.

This is where a new feature introduced from SQL Server 2005 comes into the picture. This new feature is called Forced Parameterization.

When forced parameterization is turned on, it tells SQL Server to force the parameterization of virtually all SELECT, INSERT, UPDATE and DELETE statements. There are types of queries that cannot be forced, but these exceptions are few.

With forced parameterization turned on, SQL Server will perform fewer compilations of statements because it now has the ability to re-use more query plans than before, helping to reduce resource usage and boosting performance.

Enabling Forced Parameterization

-Enabling Forced Mode

 

ALTER DATABASE YOUR_DATABASE SET PARAMETERIZATION FORCED

 

-Enabling Simple Mode

 

ALTER DATABASE YOUR_DATABASE SET PARAMETERIZATION SIMPLE

 

Alternatively Parameterization can also be set from the Management Console: –

Select the Particular Database for which you want to change the Parameterization.

Right Click and go to Properties. Go to Database Options & change Parameterization from SIMPLE to FORCED.

 

 

 

Leave a comment

Filed under Uncategorized

Excel Formulas and CPU usage

Dear All,

Today one of Business Analyst was working on a excel file of size 2 mb and the sheet is full of formulas in it.

As the excel sheet is full of formulas it was utilizing 90% and above CPU so the Business analyst was not able to work on the computer or in the excel file.

The Business Analyst is using D330UT system so he and others are also telling that as the system is very old it will consume high CPU for Heavy formula files and changing the system is the only way-out.

I was sure though excel formula is CPU hungry, when office 2007 is getting installed on that system it means it supports the configuration

To overcome the HIGH CPU usage by the Excel file following settings has been worked.

We need to Turn off the automatic calculation

Office Button –>Excel Options —>Formulas–>Set to Manual

The as the settings configured the Business Analyst was very happy as CPU usage went to 10%.

 

Thanks

Prashant Deshpande

 

 

 

Leave a comment

Filed under Uncategorized

Establish broken Trust Relation in forest

Dear All

There was no communication in between Parent and child domains for entire night due to some firewall issue, because of which the replication in between sites does not happened and the trust relationship has broken between parent and child domain.

Following problems has been faced during broken trust relationship.

  1. Outlook was continuously asking for username and password and even after providing correct credentials it was not communicating with exchange server which is in parent domain.
  2. Nobody is able to access any network system using NetBios.

The main issue was on child domain it was not allowing to login the user with enterprise & schema admin rights which could helped to repair the trust relationship .

To resolve the issue we have put in lot of efforts with various techniques and the final solution worked is as follows

Step 1

  1. On Parent Domain controller ( where the PDC role exists ) installed ADSIedit tool
  2. From ADSIedit snap-in explore cn=system folder from left panel and delete CN=mkcl.mkclindia.local class TrustedDomain key from right panel

Step 2

  1. Explore cn=Users from left panel in ADSIedit snap-in and delete CN=MKCL$ class user from right panel

 

 

Step 3

  1. On Child Domain controller ( where the PDC role exists ) installed ADSIedit tool
  2. From ADSIedit snap-in explore cn=system folder from left panel and delete CN= mkclindia.local class TrustedDomain key from right panel

 

 

Step 2

  1. Explore cn=Users from left panel in ADSIedit snap-in and delete CN=MKCLINDIA$ class user from right panel

 

Now Restart both the servers parent domain controller and child domain controller.

After restarting both the servers on parent Domain controller open Active Directory Domains and Trusts snap-in

In the Domain and trusts Snap-in -> right on Parent domain and select properties

In the Trusts windows we will not see the trust name between child and domain so to create a new trust click on new trust and provide the appropriate credentials which will establish new trust relationship between parent and child domain.

Thanks

Prashant Deshpande

 

 

4 Comments

Filed under Uncategorized