A blog about SQL Server, Books, Movies and life in general
Thursday, December 29, 2016
The 15 most popular posts in 2016
Another year is almost over. As the last post of this year I decided to share with you the 15 most popular posts in 2016. I just used Google Analytics to grab this info, I excluded the home page from the top 15. Four of these posts were written in 2006 and one was written in 2005
Here they are in order of popularity
1 Some cool SQL Server announcements SQL Graph, Adaptive Query Plan, CTP1 of SQL vNext, SQL Injection detection
This is my recap of the chalkboard session with the SQL Server team at the SQL Server PASS summit in Seattle.
2 Five Ways To Return Values From Stored Procedures
A very old post that shows you five ways to return values from a stored proc
3 SQL Server 2016 SP1 released, SQL Server vNext available for download
Posted this while the connect event was going on
4 Installing SQL Server on Linux, connecting and running queries from Linux
A quick walk through on how to install SQL Server on Ubuntu, I also included a video in the post
5 Are your foreign keys indexed? If not, you might have problems
A post showing you that indexing foreign keys might be beneficial if you do deletes
6 Convert Millisecond To "hh:mm:ss" Format
A very old post showing you how to convert from milliseconds to "hh:mm:ss" format
7 What's new in SQL Server 2016: CREATE OR ALTER
A post showing how the new CREATE OR ALTER syntax works
8 Why you need additional privileges to truncate tables compared to delete statements
The reason why you need these privileges is shown
9 What's new in SQL Server 2016: Truncate partitions
This post shows how truncate partitions works
10 What we learned from the reddit SQL Server AMA thread on /r/sqlserver
A recap of the AMA SQL Server thread on reddit
11 Playing around with the new stuff in SQL Server vNext and SQL Server 2016 SP1
Just a post showing some of the new stuff released in SP 1 of SQL Server 2016 and vNext
12 ISO-11179 Naming Conventions
A very old post linking to the ISO-11179 Naming Conventions document
13 Query Analyzer Trick
A very old post explaining how you can drag the columns into the query window from object explorer. I think by now everyone knows this one right?
14 What was the first computer you bought, what were some of your favorite games?
Mine was the Commodore 128, what about you?
15 Chaos Isolation Level In SQL Server
This is linked from dba.stackexchange.com, it is kind of interesting because it was a silly post I made
That is all... see you in 2017...
Tuesday, December 27, 2016
Didn't go to PASS but want to watch the sessions?
You didn't go to the PASS summit this year, but you would still want to watch the sessions? There is a way now, you can buy the USB stick with all the sessions, you can also download the sessions you are interested in. The passboutique site has the details, They also have a sale going on at the moment.
So if you got a bunch of money over the holidays, this would be a great investment.....
Saturday, December 17, 2016
SQL Server vNext version Community Technology Preview 1.1 available
A nice holiday present for you all has just arrived: SQL Server next version Community Technology Preview 1.1
Here is what is new in terms of the SQL Engine
- Language and performance enhancements to natively compiled T-SQL modules, including support for OPENJSON, FOR JSON, JSON built ins as well as memory-optimized tables support for computed columns.
- Improved the performance of updates to non-clustered columnstore indexes in the case when the row is in the delta store.
- Batch mode queries now support “memory grant feedback loops,” which learn from memory used during query execution and adjusts on subsequent query executions; this can allow more queries to run on systems that are otherwise blocking on memory.
- New T-SQL language features:
- Introducing three new string functions: TRIM, CONCAT_WS, and TRANSLATE
- BULK IMPORT supports CSV format and Azure Blob Storage as file source
- STRING_AGG supports WITHIN GROUP (ORDER BY)s
So finally we can write the following instead of doing LTRIM and RTRIM
TRANSLATE
SELECT TRIM( ' NoSPaces ') AS Result;
That will return just the value NoSpaces
You can also specify what characters to trim
This acts like a bunch of replace statements, instead of REPLACE(REPLACE(REPLACE(REPLACE(SomeVal,'[','('),']',,')'),'{','('),'}',,')') you can do the following which is much cleaner
SELECT TRANSLATE('2*[3+4]/{7-2}', '[]{}', '()()');
Running that will return 2*(3+4)/(7-2)
Everything that is new in version 1.1 can be found here https://msdn.microsoft.com/en-us/library/mt788653.aspx
What are you waiting for??..... pick where you want to install the next version.....
- Install on Red Hat Enterprise Linux
- Install on Ubuntu Linux
- Pull and run a Docker container on Linux, Windows, or macOS
- Download the preview for Windows
- Create a SQL Server on Linux virtual machine in Azure
See...you won't be bored this weekend.....
Friday, December 16, 2016
Interesting SQL related links for the week of Dec 16, 2016
Here are some interesting articles I read and tweeted about this past week, I think you will like these as well. If you are bored this weekend, some of these might be good for you to read
SQL Server + Python — What’s new
We recently announced SQL Server v.Next CTP1 on Linux and Windows, which brings the power of SQL Server to both Windows and — for the first time ever — Linux. You can now connect your applications to SQL Server running on Linux, Windows and macOS (with Docker).
For our Python developers, we have a few updates that will improve Python connectivity with SQL Server. Pyodbc is now:
- Ability to insert into, update and filter by values stored in encrypted columns from a Query Editor window.
- The new online encryption algorithm, exposed in the Set-SqlColumnEncryption PowerShell cmdlet, which makes tables available for both reads and writes during the initial encryption and column encryption key rotation.
This article addresses the first of the two enhancements.
Friday, December 09, 2016
Connecting to SQL Server when your default database is not available
One of our database on the development went in suspect mode today. This database was the default for a bunch of logins. These people could not login now. Someone needed to use a different database but he couldn’t login because the database that was in suspect mode was the default database for the login he was using.
I told this person to click on the Options button in the connection dialog and specify another database. I guess there was an misunderstanding because this person couldn’t get it to work. This means it is time for a blog post.
Let's take a look how this all works
Here is a script that will create 2 databases
CREATE DATABASE Good GO CREATE DATABASE OopsBad GO
Now create a new login named TestLogin with a password of Test. We are also adding the login we just created to the OopsBad database and we will make the login part of the db_owner role
USE [master] GO CREATE LOGIN [TestLogin] WITH PASSWORD=N'Test', DEFAULT_DATABASE=[OopsBad] USE [OopsBad] GO CREATE USER [TestLogin] FOR LOGIN [TestLogin] GO USE [OopsBad] GO ALTER ROLE [db_owner] ADD MEMBER [TestLogin] GO
Add the login we just created to the Good database as well and make the login part of the db_owner role
USE [Good] GO CREATE USER [TestLogin] FOR LOGIN [TestLogin] GO USE [Good] GO ALTER ROLE [db_owner] ADD MEMBER [TestLogin] GO
Make sure that you can login with the TestLogin account
Now that you know that you can login with the TestLogin account, use another account and put the OopsBad database in offline mode
Take the database online
ALTER DATABASE OopsBad SET OFFLINE
Now if you try to login with the TestLogin account, you will see the following error
Here is what you need to do, on the connect to server window, click on the Options button
One the next screen, click on the Connection Properties tab, do not click on Browse server... from the drop down. If you click that you might have to wait for a long time
Instead just type in the database name, in our case the database name is Good
Click on Connect and you should be in.... That's it, pretty simple, you just have to know where to find it.
Interesting SQL related links for the week of Dec 09, 2016
Here are some interesting articles I read and tweeted about this past week, I think you will like these as well. If you are bored this weekend, some of these might be good for you to read
Transaction Commit latency acceleration using Storage Class Memory in Windows Server 2016/SQL Server 2016 SP1
This enhancement is especially valuable for workloads which require high frequency, low latency update transactions. These app patterns are common in the finance/trading industry as well as online betting and some process control applications.
As many of you know, I publish a bi-weekly newsletter to more than 13,500 subscribers that contains an editorial on a SQL Server topic, a demo video, and a book review of my most recently completed book. We’re making all the 2015 demo videos available so everyone can watch them – 25 videos in all, mostly in WMV format. I did the same thing the last few years for the 2014 videos, 2013 videos, 2012 videos, and 2011 videos.
Query completion is a prerequisite for the availability of an actual query plan, but with LQS (Live Query Statistics), you can already get information about in-flight query executions (see this blog post), which is especially useful for long running queries, and queries that run indefinitely and never finish.
To look at in-flight query executions, the query execution statistics profile infrastructure must be enabled on demand.
Over the years, I’ve had many inquiries about the slides, T-SQL scripts, and additional troubleshooting information. I’m happy to report that I’ve finally collated all of the associated content from that presentation!
After installation you can run the Stored Procedure as follows:
EXEC sp_WhatsupQueryStore @dbname, @timewindow, @topqueries, @return_store_config, @return_forced_plans, @return_top_duration, @return_top_cpu, @return_top_log_read, @return_top_log_write, @return_top_phys_read
The columnstore technology is available in two flavors; clustered columnstore index (CCI) for DataMart analytics workloads and nonclustered columnstore index (NCCI) to run analytics queries on operational (i.e. OLTP) workload. Please refer to NCCI vs CCI for the differences between these two flavors of columnstore indexes. The columnstore index can speed up the performance of analytics queries up to 100x while significantly reducing the storage footprint. The data compression achieved depends on the schema and the data, but we see around 10x data compression on average when compared to rowstore with no compression. This blog will focus on Analytic workloads using CCI but cover NCCI in a future blog.
Here are some freshly written scripts for a classic DBA question: what’s going on in my stats?
With the release of SQL Server v.Next public preview on Linux and Windows, the ability to connect to SQL Server on Linux, Windows, Docker or macOS (via Docker) makes cross-platform support for all connectors, including the JDBC driver, even more important. To enable Java developers to use the newest SQL Server features, we have been updating the JDBC driver with client-side support for new features, including Always Encrypted and Azure Active Directory Authentication.
Microsoft R Server 9.0, Microsoft's R distribution with added big-data, in-database, and integration capabilities, was released today and is now available for download to MSDN subscribers. This latest release is built on Microsoft R Open 3.3.2, and adds new machine-learning capabilities, new ways to integrate R into applications, and additional big-data support for Spark 2.0.
This release includes a brand new R package for machine learning: MicrosoftML. This package provides state-of-the-art, fast and scalable machine learning algorithms for common data science tasks including featurization, classification and regression. Some of the functions provided include:
This release includes a brand new R package for machine learning: MicrosoftML. This package provides state-of-the-art, fast and scalable machine learning algorithms for common data science tasks including featurization, classification and regression. Some of the functions provided include:
- Fast linear and logistic model functions based on the Stochastic Dual Coordinate Ascent method;
- Fast Forests, a random forest and quantile regression forest implementation based on FastRank, an efficient implementation of the MART gradient boosting algorithm;
- A neural network algorithm with support for custom, multilayer network topologies and GPU acceleration;
- One-class anomaly detection based on support vector machines.
This release will bring support for Elastics Pools and Azure AD Authentication among other new features. We are also working on handling monitoring of larger number of databases. We are expecting to improve the scale by the time we RTM. Here are some numbers to give you an idea for this public preview:
With the introduction of the Temporal feature in SQL 2016 and Azure SQL Database, there is an ability to time travel through the state of data as it was at any given point of time. Alongside In-Memory OLTP, Temporal on memory optimized tables allows you to harness the speed of In-Memory OLTP, and gives you the same ability to track history and audit every change made to a record. Temporal added to memory optimized tables also allows you to maintain a “smaller” memory optimized tables and thereby a smaller memory footprint by deleting data that isn’t “hot” anymore from the current memory optimized table, which in turn moves it to the history table without having an external archival process to do that.
When memory optimized and temporal tables are combined, an internal memory optimized table is created in addition to the history table, as depicted in the diagram below. Data is flushed asynchronously from the internal in-memory History table to the disk based history table. The flush interval isn’t currently configurable. Data is flushed when the internal table reaches 8% of the memory consumed by the current table, OR you can flush it manually by executing the procedure sys.sp_xtp_flush_temporal_history. The internal memory optimized table is created with the same column definitions as the current in-memory table, but with a single index.
As requested by the community to complete the per-operator information, starting with SQL Server 2016 SP1 we are now exposing memory grant per grant iterator (such as Sorts and Hash Matches). These give you added insight into memory grants, and how overall memory usage is driven throughout execution.
Friday, December 02, 2016
Interesting SQL related links for the week of Dec 02, 2016
Here are some interesting articles I read and tweeted about this past week, I think you will like these as well. If you are bored this weekend, some of these might be good for you to read
Microsoft is putting reprogrammable chips into millions of servers
Some giants like Microsoft are also using alternative silicon to execute their neural networks after training. And even though it’s crazily expensive to custom-build chips, Google has gone so far as to design its own processor for executing neural nets, the tensor processing unit.
Lock Pages in Memory and Instant File Initialization privileges are couple of configuration settings which every DBA, SQL Server consultant or enthusiast have it in their checklist to ensure they see a predictable performance for their SQL Server instance. While Lock Pages in Memory privilege information is logged in SQL Error log, Instant File initialization information was first introduced in SQL Errorlog starting SQL Server 2016 RTM and later added to SQL Server 2014 with SP2.
When you are managing, administering or monitoring large deployment of SQL Servers, it is still cumbersome to programmatically query SQL Error log to check if these permissions are enabled for the SQL Server service account. With SQL Server 2016 SP1, we have added new columns in the DMV which now makes it easy to develop scripts to programmatically query and report whether Lock Pages in Memory and instant file initialization privileges are enabled on a given instance of SQL Server.
SQL Server uses memory to store in-transit rows for hash join and sort operations. When a query execution plan is compiled for a statement, SQL Server estimates both the minimum required memory needed for execution and the ideal memory grant size needed to have all rows in memory. This memory grant size is based on the estimated number of rows for the operator and the associated average row size. If the cardinality estimates are inaccurate, performance can suffer:
- For cardinality under-estimates, the memory grant can end up being too small and the rows then spill to disk, causing significant performance degradation compared to a fully memory-resident equivalent.
- For cardinality over-estimates, the memory grant can be too large and the memory goes to waste. Concurrency can be impacted because the query may wait in a queue until enough memory becomes available, even though the query only ends up using a small portion of the granted memory.
We recently announced SQL Server v.Next CTP1 on Linux and Windows, which brings the power of SQL Server to both Windows — and for the first time ever — Linux. Developers can now create applications with SQL Server on Linux, Windows, Docker, or macOS (via Docker) and then deploy to Linux, Windows, or Docker, on-premises or in the cloud.
As part of this announcement, we have released new SQL tools and also updated existing SQL tools. Developers can use these tools to connect to and work with SQL running anywhere, including SQL Server on Linux, Windows or Docker.
Subscribe to:
Posts (Atom)