Browsed by
Category: On designing

Using table-valued parameters over linked servers

Using table-valued parameters over linked servers

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…

Read More Read More

Offloading read load on multiple database replicas

Offloading read load on multiple database replicas

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…

Read More Read More

To (force)seek or not to (force)seek

To (force)seek or not to (force)seek

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…

Read More Read More