Tagged Questions
0
votes
2answers
39 views
Dropping and recreating indexes
I most often come in a situation where the users move there database from one server to another and immediately after they move they face performance problems(slowness).
I somehow manage to rebuild ...
0
votes
0answers
78 views
how to find similar word with more similarities
how to Find words with length less than or equal...
declare @inp nvarchar(max),@data nvarchar(max)
set @inp='You can dance, you can jive, having .... jove... jve, ...'
set @data = 'jeve'
...
2
votes
2answers
103 views
Is there a difference in performance between @date and getdate()?
Usually I use the getdate() function in my where clauses to go back in time.
Something like:
DOC.DATUM >= DATEADD(DD,-1*SSN_SDO.DANA_ZA_POVRAT,GETDATE())
Will SQL Server 2008R2 perform faster ...
2
votes
3answers
97 views
How can I query data from a linked server, and pass it parameters to filter by?
I have a really big query that needs to be run on multiple databases, and the results appended to a temp table and returned.
The basic syntax looks something like this:
INSERT INTO #tmpTable (Id, ...
0
votes
1answer
91 views
What is the correct model for related tables with millions of rows?
I need to create a question and answer tables that will have millions (maybe billions) of rows.
The current model is:
Question Table
id_question (PK, auto increment)
id_user
question_content
...
4
votes
3answers
218 views
Query runs slow in test site on first execution. Why?
I found this query by watching a test site with sql profiler for anything taking over 10 seconds. I plopped the code right out of sql profiler and into sql studio, where it was able to execute ...
4
votes
2answers
115 views
Is high Extent Fragmentation a problem?
DBCC SHOWCONTIG scanning 'MyTable' table...
Table: 'MyTable' (2048062382); index ID: 1, database ID: 28
TABLE level scan performed.
- Pages Scanned................................: 1019182
- Extents ...
3
votes
1answer
99 views
How does order of index affect performance in SQL Server?
I am wondering how does order of index affect performance (ASC, DESC) ? If it affects, why ? And which order should I choose ?
6
votes
4answers
300 views
Gaps and islands: client solution vs T-SQL query
Can a T-SQL solution for gaps and islands run faster than a C# solution running on the client?
To be specific, let us provide some test data:
CREATE TABLE dbo.Numbers
(
n INT NOT NULL
...
1
vote
1answer
86 views
SQL Server 2008 search joining query
I have two table as one is the message table and another one is messageUser table.
Now i need to check before insert a new row.
for example,
M_MessageMessageId=========
...
1
vote
3answers
168 views
Why is SQL running the same query longer from another connection?
Here is the issue overview: Why does my stored procedure run faster when executed localy vs remotely?
Dont jump to any conclusion just yet, let me explain what I mean...
Here is the setup:
A Windows ...
4
votes
1answer
64 views
Covering Index Question
I have the following query
Select Pt.PRODID, PT.INVENTREFID, Inv.ItemName, PT.ItemID, Configid, Pt.QTYSCHED,
PT.DLVDATE, Pt.CREATEDDATETIME, pt.SCHEDEND,
CASE Left(PT.INVENTREFID, 3)
WHEN ...
0
votes
2answers
153 views
Large time difference between two almost identical simple queries…why?
I'm trying to understand why is there so much difference in execution time and CPU usage between two simple queries that only differ in a computation. The queries are the following:
SELECT ...
13
votes
4answers
453 views
Why would I NOT use the SQL Server option “optimize for ad hoc workloads”?
I've been reading some great articles regarding SQL Server plan caching by Kimberly Tripp such as this one:
http://www.sqlskills.com/blogs/kimberly/plan-cache-and-optimizing-for-adhoc-workloads/
Why ...
2
votes
1answer
55 views
Are there differences in performance of getting data between FullText Search and indexed columns
In SQL Server 2008 R2 I have a query which looks like
create stored procedure give_me_art @filter varchar(15),@skl_id int
begin
select
id,naziv,sifra,isnull(lager.kolicina,0) as lager
from art
...
5
votes
1answer
110 views
Logical reads amount difference with almost identical indexes
I'm trying to figure out why two identical queries are taking slightly different times when executed under two indexes that are almost identical, in SQL Server.
The mentioned query is quite simple, ...
0
votes
1answer
76 views
Parameter triggers different execution plan, but why?
I have a table
CREATE TABLE [dbo].[Numbers]
(
[Date] [date] NULL,
[Time] [time](3) NULL,
[Value] [char](10) NULL
)
and the table has > 10 Billion rows, therefore it ...
4
votes
1answer
101 views
In SQL Server, what are the typical symptoms of parameter sniffing?
There is a wealth of information on the DBA.SE about what parameter sniffing is and how it relates to individual query performance circumstances. I was hoping that someone could provide some sort of ...
2
votes
1answer
142 views
Optimize simple query in SQL Server
This should be a quite easy query, but I honestly think its execution time can be improved.
select idTag,MAX(pctimestamp) AS PCTIMESTAMP,getdate() AS NOW, datediff(SECOND,MAX(pctimestamp),getdate()) ...
1
vote
1answer
69 views
Dealing with large log files
I'm new to the database world and have recently started working with a large database with several tables with mainly varchar text and integers. The two largest tables are of ~50 million and ...
3
votes
1answer
197 views
Slow SQL Server query using a DB2 linked server in virtual environment
I have a problem with a slow query which uses a linked server. Let me list out my environment first and I'll get to the issue:
Virtual Windows 2003 R2 server (fresh deployments on XenServer 6.1 ...
3
votes
1answer
237 views
Need help with long running query
UPDATE: Adding a clustered index to each work table covering all fields reduced the query run times to su-bsecond. I consider this question closed.
Thanks for taking the time to read this post - ...
3
votes
2answers
1k views
How does paging work with ROW_NUMBER in SQL Server?
I have an Employee table that has one million records. I have following SQL for paging data in a web application. It is working fine. However what I see as an issue is - the derived table tblEmployee ...
4
votes
2answers
176 views
Performance on SQL Join
I have a query which takes ages to run mainly due to a join between 2 tables on a PK and FK.
The Key is prefixed with 2 letters (which is always the same, RN) followed by 7 digits
So RN1234567 for ...
0
votes
0answers
44 views
Query to fetch missing siblings [closed]
DECLARE @AName VARCHAR(100)
DECLARE @BName VARCHAR(100)
DECLARE @CName VARCHAR(100)
SET @AName = ''
SET @BName = ''
...
2
votes
2answers
469 views
Why won't SQL Server optimize the UNIONs?
Consider these queries (SQL Fiddle):
Query 1:
SELECT * INTO #TMP1 FROM Foo
UNION
SELECT * FROM Boo
UNION
SELECT * FROM Koo;
Query 2:
SELECT * INTO #TMP2 FROM Foo
UNION
SELECT * FROM Boo
UNION ALL
...
9
votes
1answer
608 views
SQL query for combinations without repetition
I need a query which can be used in (or as) a function and retrieves all combinations of n values. And I need all combinations of length k where k = 1..n.
Extended sample input and result so input ...
-3
votes
2answers
270 views
Optimisation sub query SQL Server
I would like to optimise the query below because it's actually taking 40% of the time in the real one. I HAVE to get the first line in the table 2 for each line in the table 1. The relationship is 1 ...
2
votes
0answers
79 views
How To Analyse Index Scans
I am using merge replication with SQL Server 2012.
During a sync using a merge agent there are a large number of index scans. I can see this by running a profile capturing SCAN:Started and ...
5
votes
2answers
252 views
What can I do to speed up this SQL Query?
I've created this SQL query with the help of @Dems :-)
Here is some detail, I tried to make a SQLFiddle but I kept getting errors with my variables... This works in Sql Server 2008... My question ...
1
vote
1answer
364 views
Query performance of a NULL vs an empty ('') varchar in SQL Server 2012
I have 3 indexed fields in a query: int, int, and varchar(250).
The query performs well when all 3 conditions are specified with real values. The int columns always have values, but there are plenty ...
6
votes
4answers
1k views
What can speed up a SQL count query?
When doing a count (aggregate) SQL query, what can speed up the execution time in these 3 database systems? I'm sure many things could speed it up (hardware for one), but I'm just a novice DBA, so ...
5
votes
1answer
492 views
Optimisation of complex query - 100% cost index seek?
I'm a c# developer, however I'm quite experienced with SQL. I have had a bit of experience with optimising queries but I've come up across one that has me stumped.
The query takes about 3 minutes to ...
9
votes
3answers
332 views
Are these two queries logically equivalent?
Are these two queries logically equivalent?
DECLARE @DateTime DATETIME = GETDATE()
Query 1
SELECT *
FROM MyTable
WHERE Datediff(DAY, LogInsertTime, @DateTime) > 7
Query 2
SELECT *
FROM ...
4
votes
1answer
114 views
MI Data Warehouse Advice
I have recently started a new job and part of my remit is to try to rescue the Management Information (MI) Data Warehouse. I use the term Data Warehouse very loosely here!
The server setup is:
...
9
votes
2answers
897 views
How (and why) does TOP impact an execution plan?
For a moderately complex query I am trying to optimize, I noticed that removing the TOP n clause changes the execution plan. I would have guessed that when a query includes TOP n the database engine ...
4
votes
1answer
178 views
How to Optimise Query
I have a database structure similar to this,
CREATE TABLE [dbo].[Dispatch](
[DispatchId] [int] NOT NULL,
[ContractId] [int] NOT NULL,
[DispatchDescription] [nvarchar](50) NOT NULL,
...
9
votes
4answers
790 views
Is it possible to increase query performance on a narrow table with millions of rows?
I have a query that is currently taking an average of 2500ms to complete. My table is very narrow, but there are 44 million rows. What options do I have to improve performance, or is this as good as ...
2
votes
0answers
108 views
end user complaining poor performance of a query or application in SQL server [closed]
Any suggestions on what set of questions I should be asking an end user or an application user to drill down to the real issue of his complaining about slow responses to his queries?
For example:
...
1
vote
1answer
303 views
SQL Server Query: Inefficient where clause
I have the following SQL query:
select *
from NETWORK n
inner join vwNETWORK_KEYMSTN km on n.Network_ID = km.Network_ID
inner join vwAPPROVAL_LATEST a on n.Network_ID = a.Network_ID
inner join ...
3
votes
2answers
186 views
which one is more efficient query?
I have a table called STUDENT
+-----------+-----------+-----------+---------------+
| StudentID | FirstName | LastName | EnrollmenDate |
+-----------+-----------+-----------+---------------+
| ...
6
votes
1answer
898 views
OPTION FORCE ORDER improves performance until rows are deleted
I have a somewhat complex SQL Server 2008 query (about 200 lines of fairly dense SQL) that wasn't performing as I needed it. Over time, performance dropped from about .5 seconds to about 2 seconds.
...
2
votes
1answer
99 views
Where to place non clustered index
Execution plan clearly showing that one of my table used in query is useing Clustered Index Scan. From this node, how can i guess that which columns should be part of my non clustered index key and ...
2
votes
0answers
230 views
What does duration measurement in sql server actually measure?
What precisely does the query duration measure when conducting a profiler trace?
I have a stored procedure which performs data access (SELECT), and it is taking an average of around 30 seconds to ...
-3
votes
1answer
213 views
Single query to copy data from 3 tables into a single empty table
In an interview today, I was asked if I could write a single query to copy data from 3 source tables to an empty destination table.
I started saying "I will use a temp table or table variables", ...
6
votes
3answers
2k views
Improve performance of sys.dm_db_index_physical_stats
During a maintenance job, I'm trying to get a list of fragmented indexes. But the query is extremely slow and takes over 30 minutes to execute. I think this is due to a remote scan on ...
2
votes
0answers
69 views
In KB2658214, how does SQL Server know that “correlated AND predicates” are actually correlated?
While diagnosing SQL Server 2008 R2 queries with poor cardinality estimation (despite simple indexing, up-to-date statistics, etc.) and hence poor query plans, I found a perhaps-related KB article:
...
9
votes
2answers
3k views
Slow Performance Inserting Few Rows Into Huge Table
We have a process that takes data from stores and updates a company-wide inventory table. This table has rows for every store by date and by item. At customers with many stores, this table can get ...
15
votes
2answers
504 views
T-SQL query using completely different plan depending on number of rows I'm updating
I have a SQL UPDATE statement with a "TOP (X)" clause, and the row I'm updating values in has about 4 billion rows. When I use "TOP (10)", I get one execution plan that executes almost instantly, but ...
6
votes
2answers
466 views
Parameter Sniffing: Why Does This Become An Issue?
Today, I had an issue with a SPROC Timing Out (took longer than 30 seconds) when it was run from an ASP.NET webpage, but executed quickly when run from SSMS (took 5 seconds).
After suspecting ...
