You have read that Microsoft came up with this new thing .. Hekaton they say. The new kid on the block. (Not that new actually .. been there for the better part of the last 10 years, but people still do not know too much about it) And from time to time people blog about it. Some say that it can fix TempDB issues. So let’s see then, what can and what can’t it do for TempDB: Switch table variables…
One of the biggest challenges that I had to solve is how to call stored procedures over linked servers using TVPs. In an earlier article I explained how we could offload traffic to secondary readable servers by using linked servers. However, when we deal with stored procedures using TVPs, things get a little bit more complicated. Table-valued parameters are not supported over linked servers. This is a constraint coming with SQL Server. To solve it, we need to recreate the…
The problem Since the release of SQL Server 2012, Microsoft made good on their promise of easily allowing read only workloads to be routed to the secondary. This is easily achieved by configuring AG routing in such way so that by changing the application’s connection string to Application Intent=Read-only the calls will be routed to the preferred secondaries. However for many legacy (or not) applications, this is more difficult to do than it looks. It’s rare to find applications that…
Everybody does it, but nobody admits it. Forceseek is a forbidden word in the DBA world. In the world of experts, query hints should be the last resort for fixing performance issues. And is true, with a good data model, good code and good maintenance, the query optimizer should come up with a very good plan. But good data models are hard to come by in real life, good code gets bad in time and maintenance is but a dream…
Sometimes there is the need to log the startup parameters of a stored procedure. Or maybe be able to send them to a service broker for asynchronous execution. One simple way to do it is using the for xml clause. Of course there is the need for some manual work (or maybe not). The code however would look like this: Now the @params variable is ready to be used / logged / sent forward.
In one of my earliest interactions with SQL Server I was working at a company dealing with financial application. I was part of a team with C/C++ background. So before any solid understanding of SQL, our database code was mostly looking like C code, full of operations, procedure invocations, functions. We observed with panic that we could not debug our code, not easily. So came the need of having our faithful stack traces. In time, our knowledge of sql improved,…