This is SQL 2014 on Windows Server 2012.
The server is lightly used but my query is performing very poorly.
I suspect table scans are occurring. What is the best way to identify the
problem, other than a Profiler trace?
Thanks, Jack
Do you mean that you don't know which of your queries has poor performance (in which case, using profiler would be one way to identify it), or do you mean that you have a query that has poor performance?
If it is the latter, the first thing to do is to run the query from SSMS after turning on the query plan (control-M). That will let you see which parts of the query are taking up most of the resources, and will show table scans.
Some useful info here.
Please move on this https://redmondmag.com/articles/2013/12/11/slow-running-sql-queries.aspx here you can see the steps that you need to perform to troubleshoot the query running poorly. Hoping it may solve your problem.
I'm an old-school dog ... but I don;t find the Graphical display of the Query Plan in SSMS useful. I have to mouse-hover over too many popping-up windows to see the "detail" (or I'm doing it wrongly! and this old dog could learn a new trick 
)
So I use the text output as follows
-- SET SHOWPLAN_TEXT ON
GO
-- SET STATISTICS IO ON; SET STATISTICS TIME ON
-- ... your test query here ...
SET STATISTICS IO OFF; SET STATISTICS TIME OFF;
GO
SET SHOWPLAN_TEXT OFF
GO
and then I highlight from either the Statistics or the Showplan command to the bottom.
I do the Statistics one first. This actually runs the query (which the Show Plan does NOT) and shows the results together with the number of table scans and logical I/O (I ignore any Physical I/O figures - they are dependent on Cache). I also see the Elapsed CPU for each step.
From that, alone, I often see which sub-query (e.g. within a Stored Procedure) has an unexpectedly high number of SCANS and/or I/O, and I try a different query-syntax, or create an index as a test. Often at this point I am surprised that a particular index does not exist! as it must have been overlooked in the Design phase.
Then I use the Query Plan and I look at which index is being used to resolve each part of the query. Specific things I look for are:
The Clustered Index (i.e. usually the Primary Key) being used to resolve a column which is NOT in that index. This will be a whole-table-scan.
Sensible index being used but a Scan rather than Seek is being used. This isn't always bad, but it is worth investigating.
Usually that those "acts" solve pretty much all my query-tuning problems.
Thanks Jason and Kristen.
"SET SHOWPLAN_TEXT ON" is what I used today.
In the end, I created a statistic on the column I used for the partitioning. Then updated stat.
Much to my surprise, the query that had been running for 5 minutes, then returned the resultset in 2 seconds! I hope this helps someone else struggling with clustered columnstore indexes.
Just a suggestion. Never use SET STATISTICS if either a scalar or multi-statement table valued function i(mTVF) is involved. It makes it seem sometimes hundreds of times worse than it actually may be. You can also use an iTVF (INLINE Table Valued Function) as a mythical but high performance iSF (INLINE Scalar Function). Please see the following article for amplification on both of those subjects.
http://www.sqlservercentral.com/articles/T-SQL/91724/
Thanks Jeff, didn't know that and I've modified the comments in my "Performance Test Snippets File" accordingly. I've definitely seen "weird" CPU timing results in the past and just ignored them (rather than trying to optimise them out!!) as they were clearly different from reality. I'll know the cause next time.
The code I posted was abridged, here's my full code ... but ... I'm not sure when, if at all?, it is appropriate to use
SET STATISTICS PROFILE ON
or
SET SHOWPLAN_ALL ON
or
SET STATISTICS PROFILE ON; SET SHOWPLAN_TEXT ON
so although they are in my snippets file, because I must have read about therm somewhere, at some time!!, I don't think I have actually used them.
-- Clear cache (for level playing field
-- - only if wanting to check PHYSICAL performance rather than LOGICAL performance)
-- Do **NOT** run these on a Live server
DECLARE @dtStart datetime = GetDate()
DBCC DROPCLEANBUFFERS
SELECT [DROPCLEANBUFFERS] = DATEDIFF(second, @dtStart, GetDate())
GO
DECLARE @dtStart datetime = GetDate()
DBCC FREEPROCCACHE
SELECT [FREEPROCCACHE] = DATEDIFF(second, @dtStart, GetDate())
GO
-- Execute the SHOWPLAN to see the Query Plan, OR the STATISTICS to see the logical stats
-- SET STATISTICS PROFILE ON; SET SHOWPLAN_TEXT ON -- If Temp Tables involved
-- SET SHOWPLAN_ALL ON -- Complex output - multiple columns
-- SET SHOWPLAN_TEXT ON -- Simple output - just the Query Plan
GO
-- SET STATISTICS PROFILE ON -- Complex output - With Actuals - multiple columns
-- NOTE: "SET STATISTICS TIME ON" may DRAMATICALLY increase execution time and thus any CPU measurements may be unrealistic
-- SET STATISTICS IO ON; SET STATISTICS TIME ON -- Just Logical Reads and SCANs
DECLARE @StartTime datetime = GetDate();
-- ... put query here ...
SELECT [Elapsed(ms)] = DATEDIFF(ms, @StartTime, GetDate())
SET STATISTICS IO OFF; SET STATISTICS TIME OFF; SET STATISTICS PROFILE OFF
GO
SET SHOWPLAN_TEXT OFF
GO
SET SHOWPLAN_ALL OFF
GO
My snippet does include an elapsed-time calculation, so I can use that (instead of STATISTICS TIME) when appropriate, but it does mean clearing cache etc. in order to get a repeatable, non-cached, execution time - which is not a good idea on a production server of course.
Re: your article's suggestion to use iSFs instead of UDFs
I became aware of the poor performance of UDFs in our code long after we had adopted them "wholesale" 
My solution, still not implemented, was to write a pre-processor to code-them-out. Currently when we push code from DEV to QA we performance a number of steps:
Source Code (SProcs, Views, Triggers etc.) is run through a white-space and comment removal routine (to minify it). I have no idea if this helps run-time performance (we've debated it in a thread here, I've never had time to test if that is the case). It is possible that it will use less cache space, so less cache-churn. It is definitely faster to deploy the update scripts, but the main benefit, for us, is to obfuscate the code to stop some casual observer from helping themselves to our code
We also set WITH ENCRYPTION as part of deployment from DEV to QA - not for rigorous security benefits, but to stop a casual thief, or a well-meaning client from "fixing" our code
My thought has been to improve the pre-processor so that it can, additionally, convert UDFs to inline-code. But I haven't done it because I perceived it to be a lot of work. I have dozens of UDFs like
CREATE FUNCTION dbo.MyFN_YesNo2bln
(
@strValue varchar(100) -- Yes/No Value to convert [to boolean] (else NULL)
)
RETURNS tinyint
WITH SCHEMABINDING
/* , ENCRYPTION */
AS
BEGIN
RETURN CASE RTrim(@strValue)
WHEN 'Y' THEN 1
WHEN 'Yes' THEN 1
WHEN '1' THEN 1
WHEN 'No' THEN 0
WHEN 'N' THEN 0
WHEN '0' THEN 0
ELSE NULL
END
END
and converting each of those to inline-code is going to mean coding them ALL into something that a pre-processor could substitute. its-not-going-to-happen!
HOWEVER ... your article has given me the thought that I could easy improve the pre-processor so that it converts a UDF into an iSF and that looks like a REALLY promising solution 
I would need to maintain, in parallel, an iSF version of each UDF with an "obvious" naming convention
CREATE FUNCTION dbo.MyFN_YesNo2bln_iSF
(
@strValue varchar(100) -- Yes/No Value to convert [to boolean]
)
RETURNS TABLE
WITH SCHEMABINDING
/* , ENCRYPTION */
AS
-- BEGIN
RETURN SELECT [iSFResult] = CASE RTrim(@strValue)
WHEN 'Y' THEN 1
WHEN 'Yes' THEN 1
WHEN '1' THEN 1
WHEN 'No' THEN 0
WHEN 'N' THEN 0
WHEN '0' THEN 0
ELSE NULL
END
-- END
now my preprocessor just has to replace
DECLARE @TestValue varchar(10) = 'Yes'
SELECT dbo.MyFN_YesNo2bln(@TestValue)
-- with:
SELECT (SELECT iSFResult FROM dbo.MyFN_YesNo2bln_iSF(@TestValue) )
which is going to be very straightforward to parse-and-replace, and the pre-processor can check to see if there is a
@TheFunctionName + '_iSF'
in existence in the DB and only do a replace if an iSF version exists.
Apologies for the late reply.
Thanks for posting that and, yes, that should definitely help anyone that finds your good post, I've found that adequate/up-to-date statistics are actually much more important than defragging indexes.
I also apologize to you for the late reply. That's a totally awesome post you made. I really appreciate what you explained about how you've made a "pre-processor" replace the Scalar UDFs with a SELECT FROM iSF replacement. Very cool stuff.