Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Fun with sp_executeSQL Expand / Collapse
Author
Message
Posted Tuesday, January 31, 2012 12:48 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Tuesday, January 31, 2012 1:08 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 21, 2014 12:21 PM
Points: 117, Visits: 572
Post deleted.
Post #1244732
Posted Tuesday, January 31, 2012 1:14 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, August 4, 2014 8:10 AM
Points: 1,635, Visits: 1,972
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
Posted Thursday, February 2, 2012 3:30 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 8:36 AM
Points: 3,918, Visits: 3,638
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 »

Add to briefcase ««12

Permissions Expand / Collapse