Fun with sp_executeSQL

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by David Poole » Fun with sp_executeSQL

14 posts, Page 2 of 2

««1 2

Fun with sp_executeSQL

Rate Topic

Display Mode

Topic Options

Author

Message

pbarbin

Posted Tuesday, January 31, 2012 12:48 PM

Grasshopper

Group: General Forum Members
Last Login: Thursday, December 13, 2012 7:23 AM
Points: 22, Visits: 349

Great article David. I was going to ask the same question about proc cache limits, but I have heard about the "stolen pages" concept too. So, it might be that there is a limit, but still keeping the proc cache lower does give data cache more room.

I think the option to "optimize for adhoc workloads" will reduce the proc cache as I believe it stubs out the entry in the cache but doesn't actually take up all the space until the usecount moves to 2. That keeps all those "1 timers" from filling it up.

Entity Framework has exhibited some of the same problems as nHibernate. Good news, they have fixed a number of them in the latest version, but what might be worth researching is index usage. When the parameter datatype given doesn't match the datatype of the column that it's being compared to, I am pretty sure Sql Server gives up on obvious index options. So be careful what datatypes the developers are giving the params.

Post #1244719

SAinCA

Posted Tuesday, January 31, 2012 1:08 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 3:24 PM
Points: 116, Visits: 489

Post deleted.

Post #1244732

cfradenburg

Posted Tuesday, January 31, 2012 1:14 PM

SSCommitted

Group: General Forum Members
Last Login: Thursday, May 16, 2013 7:44 AM
Points: 1,559, Visits: 1,925

pbarbin (1/31/2012)

...what might be worth researching is index usage. When the parameter datatype given doesn't match the datatype of the column that it's being compared to, I am pretty sure Sql Server gives up on obvious index options. So be careful what datatypes the developers are giving the params.

That's due to implicit conversion and whether or not it results in an index not being properly used has to do with data type precedence. If the index is on something lower on the list (such as varchar) and you pass in something higher (such as int) then SQL is going to convert the value for every row from varchar to int before doing the comparison resulting in a scan. If it's the other way around (index on int and you're passing in varchar) then the implicit conversion will happen on the parameter and you can still get a seek. However, it is best to make sure the data types match so you don't need to worry about implicit conversions at all.

Post #1244739

Cliff Jones

Posted Thursday, February 02, 2012 3:30 PM

Hall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 3:56 PM
Points: 3,400, Visits: 3,411

ganci.mark (1/31/2012)

Great article David. Thank you.

I thought that there were limits imposed on the various memory components based on on the total amount of memory given to SQL Server.

For example: Proc Cache limits
SQL Server 2005 SP2
75% of server memory from 0-4GB +
10% of server memory from 4Gb-64GB +
5% of server memory > 64GB
So 14GB given to a SQL SERVER 2005 SP2 would yield a 4GB Proc Cache.

Is it correct in stating that the limits do not exist and proc cache for example can consume additional memory?

Thanks
Mark

This is my understanding as well.

Post #1246164

« Prev Topic | Next Topic »

14 posts, Page 2 of 2

««1 2

Permissions