SSWUGtv
With Stephen Wynkoop
Do you think Big Data should impact your privacy practices? Find out in this recent edition of SSWUGtv
Watch the Show]
Usage of TVP Can Fail
Table Value Parameters (TVP) are a technique for passing a table as an input value to a stored procedure or a function. They are supported in SQL Server as of release 2008.
I think this is a wonderful capability and was really glad when it was released in SQL Server 2008. Prior to TVP we had to pass sets of data to a stored procedure in the form of xml or some other string serializing technique.
I use TVP when calling a stored procedure from external applications via ADO. I also use this technique internally when calling a function or stored procedure from another stored procedure.
Recently I began to experience a lot of faults in my SQL Log (a good reason to review your logs) and a number of dump files in the SQL Server Log Directory. After a support call with Microsoft it was determined that SQL Server has a bug when handling TVP objects under certain circumstances. The Microsoft recommendation was to not use TVP,
I’m not trying to discourage you from using TVPs. I am telling you that there are unique conditions where they can fail. We had used them successfully for years without any problems. The failure occurred when the SQL Server experienced a dramatic increase in load resulting in a lot of page faults.
A TVP differs from other temporary table types in that it only lives in memory. So, when your SQL Server instance is starving for memory, it can’t be cached to disk like a temporary table. In this case, it has to use the operating system swap file to get enough memory to work with…very expensive.
I may not have this technically accurate…but the message is clear…
- Watch your server for errors…primarily, review your logs
- Apply updates
- Keep performance metrics so you can compare trends when things get bad
Cheers,
Ben
$$SWYNK$$
Featured White Paper(s)
SharePoint 2010 Enables the Enterprise
Written by KnowledgeLake
read more)