Press "Enter" to skip to content

Author: Kevin Feasel

OBJECT_ID() In Cross-Server Queries

Published 2017-02-28 by Kevin Feasel

Denis Gobo ran into a problem with a linked server query he ran:

This past week I needed to run some queries on production to verify there were indexes added on a table. There were several scripts that needed to be run and the last one was the addition of the indexes.  The query given to me was something like the following

SELECT *
FROM LinkedServerName.DatabaseName.sys.indexes
WHERE object_id =(OBJECT_ID('TableName'))

So I ran the query..nothing. Aha maybe they are still running the scripts before that, setting up replication, snapshotting the table etc etc. I will check again in a bit I thought.

Click through for the full reason and how to fix your code in this situation.

Comments closed

Published 2017-02-28 by Kevin Feasel

Amit Kulkarni shows how to install Azure Data Lake Store support on your “older” Hadoop clusters:

How old is really old?

The Azure Data Lake Store binaries have been broadly certified for Hadoop distributions after 3.0 and above. We are really in uncharted territory for lower versions. So the farther away you go from 3.0 the higher the likelihood of them not working. My personal recommendation is to go no lower than 2.6. After that your mileage may really vary.

This is a good article, and do check it out.  A very small mini-rant follows:  Hadoop version 2.6 is not old.  Nor is 2.7.  2.7 is the most recent production-worthy branch and 3.0 isn’t expected to go GA until August.

Comments closed

Upgrading SQL On Linux

Published 2017-02-28 by Kevin Feasel

Steve Jones shows how to upgrade SQL Server on Linux to the latest version:

I saw this week that there was a new CTP (v1.3) of SQL Server v.Next. I haven’t had a lot of time to work on the Linux version lately, but I thought I’d try and see how well the upgrade went.

There’s an install and upgrade page at Microsoft you can use, but on Ubuntu, things are easy. First, connect to your system and run this:

sudo apt-get update

That will download updated packages and get the system ready. you can see that I have a lot of stuff to update on this particular system.

One small change I’d make to that script in the snippet is sudo apt-get update && sudo apt-get upgrade.  They do different things, both of which are useful.  I do hope that Microsoft keeps with the Linux-friendly upgrade process when it comes to CUs and SPs.

Comments closed

Dynamic Searches In SQL

Published 2017-02-28 by Kevin Feasel

Kenneth Fisher looks at a few methods for dynamic searches in T-SQL:

Multiple Queries

Pro: We get a separate query plan for each combination of parameters so performance is great. (Well, as good as can be anyway.)
Con: Maintance stinks. We need 16 different queries when we have 4 parameters and the numbers increase dramatically as we add additional parameters. So any change we make to the base query will have to be changed 16 times, and/or adding a new parameter means careful logic and adding a bunch of new queries.

I’d consider this a gateway for Erland Sommarskog’s article on the topic.

Comments closed

The Importance Of Action

Published 2017-02-28 by Kevin Feasel

Jesse Seymour has relaunched his blog and started with a controversial statement:

There is no value in data.

If you’re still here, then I am assuming you either a) believe I have a valid point, or b) just want to see how crazy I am for opening my new data blog with a post spouting the lack of value in data.  We’ll see which option is right by the end of the post because right now, I am not so sure which one is right and which one is wrong.  After all, if there is no value in data, why should companies hire data professionals and give them a pay check?

My long-form response is too long for this format, so the short response is that data requires context.  I agree that action is important, but the purpose of a data visualization professional is to provide information with the relevant context to assist decision-making.  It’s not that there’s no value in data or that action is everything; it’s a multi-faceted process, and the specific relevant data will depend upon the industry.  In professional sports, front offices certainly use accurate(-ish) metrics which show the worst performing players on the team because sports leagues are zero-sum games.  Finding out Fred in Accounts Receivable spends the most time at the coffeemaker each day (17 minutes instead of 12 minutes!) matters a lot less, so unless you’re doing a Taylor-style factory study—and if you are, I’ll have other words with you that also aren’t apropos here—it doesn’t rate high enough in the relative priority list.

Comments closed

What Does Activity Monitor Do?

Published 2017-02-28 by Kevin Feasel

Tibor Karaszi explains each window in the Management Studio Activity Monitor:

The idea here is to show where SQL Server is waiting, “wait stats”.

It uses the same procedure as the “Waiting Tasks” diagram uses, #am_generate_waitstats, to get the information. See the above section for “Waiting Tasks” to understand the time dimension for this. For simplicity, we can say that it shows only wait stats for the past 30-60 seconds. This is important. Imagine that you had loads of a certain wait stats, but none just for the last minute. This pane can now fool you that you didn’t have any waits of that kind, just because you didn’t for the past minute. Note, though, that the “Cumulative Wait Time” column is the sum of wait in the group since SQL Server was re-started or since we last cleared the wait state (DBCC SQLPERF(“sys.dm_os_wait_stats”,CLEAR)).

In an attempt to be friendly, it will group and summarize wait stats into various groups. That would be fine if there were some documentation about which individual wait type is in each group. Also, some wait types are ignored. One of the ignored wait types is CXPACKET, another is THREADPOOL.

Activity Monitor isn’t very good, but sometimes you can’t get the good tools installed on a server and need to check something quickly.  In those cases, it’s a handy thing to know.

Comments closed

SSRS Category Charts & Ints

Published 2017-02-28 by Kevin Feasel

Kathi Kellenberger notices an oddity with SSRS Mobile Report category charts:

Notice that OrderYear displays decimal points. I switched the dataset in the Series field name property, and found that neither of the columns in the dataset can be used.

Numeric columns cannot be set as a Series name field. To work around this, I modified the dataset, casting OrderYear as a CHAR(4).

That’s not a great situation, but at least there’s a workaround.

Comments closed

Serverless Azure

Published 2017-02-28 by Kevin Feasel

Christos Matskas has an article on Azure Functions, Service Fabric, and Batch:

This service is the hidden gem of HPC (high performance computing) within the Azure Compute service family. As the name implies, Azure Batch is designed to run large-scale and high-performance computing applications efficiently in the cloud. When you’re faced with large workloads, all you have to do is to use Azure Batch to define compute resources to execute your applications in parallel and at the desired scale. A good use-case for Azure Batch would be to perform financial risk modelling, climate data analysis or stress testing. What makes Batch so useful is the fact that you don’t need to manually manage the node cluster, virtual networks or scheduling because all this is handled by the service. You need to define a job, any associated data and the number of nodes you want to utilise. It makes no difference if you need to run on one, a hundred or even thousands of nodes. The service is designed to scale according to the workload needs.

The cheapest server may very well be no server, and we’re at the point where relatively simple services could just run as Azure Functions or AWS Lambda functions.

Comments closed

Large Data Sets In Memory-Optimized Table Types

Published 2017-02-27 by Kevin Feasel

Jack Li explains a difference between memory-optimized tables and memory-optimized table types:

This customer was puzzled because he delete existing rows. At any given time, there should not be more than 1 million rows.  SQL Server should not have run out of memory.

This is actually by-design behavior documented in “Memory-Optimized Table Variables”).  Here is what is state “Unlike memory-optimized tables, the memory consumed (including deleted rows) by table variables is freed when the table variable goes out of scope)”.  With a loop like above, all deleted rows will be kept and consume memory until end of the loop.

Click through for the complete story.

Comments closed

Four-And-Twenty R Users

Published 2017-02-27 by Kevin Feasel

Ginger Grant explains why SQL Server R Services creates a group with twenty separate external users:

There are many reasons why a DBA might want to not allow clients to access server memory as that will tax the server. Turning it off is relatively simple. Go to the SQL Server Management Console and select SQL Server Launchpad for the instance of SQL Server running R Server.

In the picture of the screen, the instance of SQL Server I have running R Services is in SS2016. Right click on the server and select Properties, then click on the Advanced tab. When looking at the number of external users allowed by default, the number might look familiar. The reason there are twenty User IDs created for R Server is because Launchpad allocates by default external twenty users to connect from SQL Server to run R. If you don’t want to allow external users to run on a server, you will need to prevent the users from connecting by not enabling them to run R. To run R, users need to have db_rrerole permissions. If they do not have that, they cannot run R. On the production server, it is probably best that this permission not be granted to non-system users.

Read on for more details.

Comments closed