I remember a forum thread from a while back. The question was on how to get rid of the index scan that was in the query plan. Now that’s a poor question in the first place, as the scan might not be a problem, but it’s the first answer that really caught my attention.
Since the primary key is on an identity column, you can add a clause like ID > 0 to the query, then SQL will use an index seek.
Technically that’s correct. If the table has an identity column with the default properties (We’ll call it ID) and the clustered index is on that identity column, then a WHERE clause of the form WHERE ID > 0 AND <any other predicates on that table> can indeed execute with a clustered index seek (although it’s in no way guaranteed to do so). But is it a useful thing to do?
Time for a made up table and a test query.
Anything Gail writes is a must-read; this is no exception.
However, running DMV queries against a Power BI Desktop model (which of course runs a local version of the same engine that powers Analysis Services Tabular and Power Pivot) and more importantly doing something useful with the information they return, isn’t straightforward. You can run DMV queries from DAX Studio but that will only give you the table of data returned; you need to copy and paste that data out to another tool to be able to analyse this data. Instead it’s possible to use Power BI Desktop’s own functionality for connecting to Analysis Services to connect to its own local data model and run DMV queries.
It looks like there are some limitations to this technique, but for quick and dirty work, it works.
After many months of confusing myself with mental arithmetic, I decided to create a straightforward list based on Jonathan’s algorithm, in 4 GB increments, for my own reference. I would like to share this with you today.
It’s a nice lookup table, so thanks to Randolph for putting that together.
Map this out before you write the code. When will the jobs run, and what should happen if they fail? Should someone be engaged? What tools will they need, and when is the Service Level Agreement for when the process has to be complete? You’ll need lots of details on this to make sure your automation and documentation meet the bar.
Partitioning is an extra layer of complexity. It can be a very useful extra layer of complexity, but this is a case where it’s best to spend an hour before you begin and walk through potential issues. Those potential issues will come—automation jobs will fail, external configuration changes will affect your partition strategy, bad data will sneak in and fill up your supposedly-empty edge partitions.
This is the error text:
The ALTER DATABASE command could not be sent to the remote server instance ‘TCP://ORACLEDB.darling.com:5022’. The database mirroring configuration was not changed. Verify that the server is connected, and try again.
Alright, that’s silly. I used the GUI. Instead of going to bed I’ll spend some time checking all my VM network settings. BRB.
I’m back. They were all correct. I could ping and telnet and set up linked servers and RDP. What in the name of Shub-Niggurath is going on with this thing?
These things always happen right before bed, right before the big meeting, right before lunch. They never happen on a slow Tuesday afternoon, it seems…