A Fine Slice Of SQL Server
Kenneth Fisher is on a mission:
HAL0001 randomly stopped you from making DDL changes.
HAL0002 stopped you from using NOLOCK in code.
HAL0003 will not let you touch a given table (DiscoveryOne as it happens) and will disable your login and kill your connection if you try.
He’s got a ways to go, but I applaud his long-term vision.
Comments closedDavid Alcock sounds an important note:
All of that is very good however a word of warning has been issued from the MSSQL Tiger Team if you are using SP1 on the “lower” version:”you might see some unforeseen errors or databases might even be left in suspect state after uninstallation of SQL Server 2016 SP1. Even worse would be if the system databases are using new features for example, partitioned table in master database, it can lead to SQL Server instance unable to start after uninstalling SQL Server 2016 SP1″.
This makes sense: if you’re using new functionality and try to revert back to a version without that functionality available, there could be an issue. David links to a test script you can use to see if your database is using any new features.
Comments closedCurated SQL is taking Thanksgiving off. If you are looking for some good reading, check out Readings in Database Systems, 5th Edition.
Comments closedOnce the command has executed you can connect remotely via SSMS using the server name and the port we specified above. The database that we created in the original image will be there, along with the data that we entered!
This is where containers start to come into their own in my opinion. You can build your own custom images and quickly spin up multiple instances that already have all the databases that you require!
Containerizing databases is something I haven’t quite got my head wrapped around yet (because we want to maintain that state over time, even if the image gets deleted), so I’m interested in seeing where this series goes.
Comments closedKendra Little discusses the locks entry in sp_configure:
Each lock uses 96 bytes of memory. On the instance in question, 25,000 locks = 2,400,000 bytes.
That’s only 2.3 MB of memory devoted to locks. Even though 25K sounds like a lot, the memory footprint for that is pretty darn small.
I checked back with our questioner, and their instance has 32GB of memory. That’s a pretty small amount in the grand scheme of things (as of SQL Server 2014, Standard Edition can use up to 128GB of memory for the Buffer Pool), but 2.3 MB isn’t anything to worry about, percentage wise.
Read on for advice if you’re seeing your SQL Server instance take a very large number of locks.
Comments closedGrant Fritchey, defying the common opinion (or is he?):
Suddenly though, it was boring. I tested a few Redgate tools (SQL Compare is right there above, connected to Linux) to be sure they worked. No major issues encountered. Great. However, now, it’s just another instance of SQL Server.
I guess I could start complaining that SQL Agent isn’t there… except I’m not that big a fan of SQL Agent, and I can schedule all sorts of stuff to run in Linux using the sqlcmd command line tool.
This is a good kind of boring.
Comments closedDave Mason is looking for a way to prevent a user from running DBCC REINDEX:
After a little digging, I discovered the related database user is a member of the db_ddladmin fixed database role. Members of that role are permitted to run DBCC REINDEX. Since I have existing (more sensible) code in place for index maintenance, I don’t want the DBCC REINDEX operations to continue. Here’s the problem: I can’t find a direct way to DENY a database user from running DBCC commands. T-SQL syntax doesn’t support something like DENY DBCC TO <user> or DENY DBCC REINDEX TO <user>. MSDN documentation tells me the equivalent ALTER INDEX command requires at minimum ALTER permission on the table or view. I guessed that revoking or denying ALTER TABLE privileges might prevent a user from executing DBCC DBREINDEX, but that does not appear to be the case.
That’s painful.
Comments closedAaron Nelson has a post on using Powershell to visualize contents in the buffer pool:
On Monday Chrissy LeMaire & I Did a session called “SQL Server Cmdlets and Community Involvement” for the PowerShell 10 Year Anniversary all-day event on Channel9 on MSDN. If you jump to the 18 minutes 30 second mark of that video you’ll see me showing how to look at the Buffer Pool of your SQL Server instance, first with the Out-GridView cmdlet, then I used a function from PowerShell MVP Boe Prox ( b | t ) called Out-SquarifiedTreeMap like so:
Read on to get a link to the code.
Comments closedJames Serra knows how to get my interest:
Polybase was first made available in Analytics Platform System in March 2013, and then in SQL Server 2016. The announcement at the PASS Summit was that by preview early next year, in addition to Hadoop and Azure blob storage, PolyBase will support Teradata, Oracle, SQL Server, and MongoDB in SQL Server 2016. And the Azure Data Lake Store will be supported in Azure SQL Data Warehouse PolyBase.
With SQL Server 2016, you can create a cluster of SQL Server instances to process large data sets from external data sources in a scale-out fashion for better query performance (see PolyBase scale-out groups):
I’m excited for the future of Polybase and looking forward to vNext and vNext + 1 (for the stuff which they can’t possibly get done in time for vNext).
Comments closedDevin Knight looks at a new Power BI custom visual:
The Timeline is similar to the native slicer in Power BI but has several more customizations available.
Not surprising, this visual can only accept date values.
If you need to adjust the start date of the Timeline based on your works Fiscal Calendar that is possible in the format settings.
This is a pretty nice visual, but when I tried to use it, I remember it feeling a little limiting, particularly around drilling into date slices.
Comments closed