That first one is a cracker – it hit me once when compressing a SQL Server table (600M+ rows) on a 64 core Enterprise SQL Server. After benchmarking several other data compression activities I thought I had a basic “rule of thumb” (based on GB data size and number of rows)… of which just happened to be coincidence!
This also begs the question of why would you use low selectivity indexes? Well I can think of a few cases – but the one which stands out the most is the identification of a small number of rows within a greater collection – such as an Index on TYPE columns (ie; [ProcessingStatusFlag] CHAR(1) = [P]rocessed, [U]nprocessed, [W]orking, [F]ailed, etc)
… AND SO – lets do some testing to validate this puppy!
There’s a significant difference here, so check out Rolf’s post for the details.
Often times while troubleshooting an issue, you’ll want more details than what you can find in the application log or SQL Log. In the background, SQL Server runs a default trace which includes a lot of items to help with troubleshooting including (but not limited to) errors, warnings, and audit data. I often run the following script as a quick way to find additional details for “ERROR” items from the default trace.
Jon notes that the default trace has been put on the deprecation list, so keep that in mind if you do use it.
So why would you want to do this? Well lets say for example you have a table in a database where the clustered index has become corrupted. Let’s further say that no one mentioned this to you for .. say a year. (No judging!) So your only option at this point might be to use the REPAIR_ALLOW_DATA_LOSS of DBCC CHECKDB. But when you are done how much data has actually been lost? Can you get any of it back?
If you’ve lived a good life and are very lucky, you might recover all data this way. Otherwise, it’s a good idea to run CHECKDB more frequently and check those backups regularly as well.
When one page becomes full and a new page is needed this is reported as a page split, but this is a regular operation with no bad consequences for our queries. The problem happens with updates and non-sequential inserts, when the row needs to be inserted in the middle of the pages of the object and there is no space for this. SQL Server creates a new page, transfers half of the page data to the new page and writes the row data. This creates page fragmentation and is very bad for performance and is also reported as page split.
We can find the bad page splits using the event sql_server.transaction_log. This event monitors all the activities in the transaction log, because that we need to use with caution. We can filter the ‘operation’ field looking for the value 11, which means LOP_DELETE_SPLIT. This is the deletion of rows that happens when SQL Server is moving rows from one page to another in a page split, a bad page split.
It’d be nice to be able to find the particular query causing the page split, and it’d also be nice to find a less resource-intensive method of displaying this information.
The SQL Server documentation team is working to improve the SQL Server documentation in an effort to enable you be more successful with and gain more value from SQL Server. Please let us know your thoughts on how we can improve the documentation by taking this brief survey by March 18.
We appreciate and are listening to your feedback! Thank you.
The questions aren’t very onerous, so help out the documentation folks and fill it out.
To install Mobile Report Publisher, click the down arrow in the SSRS portal and select the Mobile Report Publisher. Download the installer and click through to the end. Install the C++ redistributable. If you have used DataZen then the designer will look very familiar.
It’s nice seeing Reporting Services all grown up.
Back to our question at hand. Since the preview just got released I have not had a chance to test this out with SQL Server. However, you can use Active Directory accounts with Linux and Unix by using Samba and PAM. I see no reason why this implementation would not also work with SQL Server on Linux, but again I have not yet had a chance to test this out. Since I already have some written installation instructions, that’s what I am providing here.
The Attunity connector for Oracle used inside of the SSIS data flow looks a little different than the typical OLE DB connector we most commonly use. Specifically, the Attunity source has two options: “Table Name” and “SQL command.” What the Attunity Oracle Source doesn’t have in this dialog box is “SQL command from variable” (like we see for an OLE DB source).
Expressions are your friend.
As a quick tip, this on is one of these tool tips, that just makes your everyday much easier. Sometimes, more than often, you run into databases that contains a huge number of tables – all listed alphabetically. This can, at times, be cumbersome and annoying to browse. SQL Server Management Studio (SSMS) actually has a feature that will assist you in getting your job done, quicker; It’s called Filtering.
My most common filter criterion is by schema, but there are several filtering options available.
When comparing the features of the Online Client with the Desktop version of Power BI, there is one very obvious difference, there is no way to create a data model in Power BI online. It is not possible to create a data model using the online client. The online client is designed to connect to an existing online source such as Sales Force or Azure DB. If you are using an existing model, there is no need to create one. When using the enterprise gateway, which uses an on-premises database such as a SQL Server, SSAS or Hana, the data model is contained within the database exposed via the enterprise gateway, so again no reason exists to create a data model. Report creation can occur either using the online client or desktop as there is compelling technical reason that I am aware of which would determine where the report is created.