Tagged Questions
0
votes
0answers
41 views
Hierarchical data best practices
I'm looking for a best practicies\databases to solve our problem:
Currently we are using SQL Server 2008 R2 with HierarchyId. It works ok, but we have a lot of performance penalties due to this ...
9
votes
5answers
276 views
Force sql server to run query conditions as written?
Im using Sql server 2008 R2 And I have this pseudo query (SP) :
select ...
from ...
WHERE @LinkMode IS NULL
AND (myColumn IN (...very long time exeted query...))
...
...
The ...
0
votes
2answers
72 views
Why does using a variable make a difference in performance?
I have a table that constantly doing insert, and I am trying to run a delete script to remove old record.
The delete script looks like this:
DECLARE @StarDate DATETIME = '2013-01-13';
DECLARE ...
0
votes
1answer
40 views
What index to add in case of join by two optional fields
I have query similar to the one below, it joins two tables by field which can have NULL value. The result matchs the data if both tables have the same data or consider the condition as optional.
...
4
votes
1answer
193 views
Indexing strategy when using the between operator SQL Server 2008
I have a large table ~25 million rows with the structure
CREATE TABLE [dbo].[rx](
[pat_id] [int] NOT NULL,
[fill_Date] [date] NOT NULL,
[script_End_Date] AS ...
4
votes
1answer
95 views
How to get the information of the optimizer of sql server?
The info like total optimization time, the time spent on each stage of optimization, the number of alternative plans and the cost of each alternative plan. Is there any way to get these info using sql ...
4
votes
2answers
220 views
Bulk Data Loading and Transaction Log
im currently working on a project which bulk import data from flat files(csv) about 18 differents files each linking to a specific table through some store procedure.
I followed the steps as advised ...
1
vote
2answers
142 views
XML Large DataBase is the best? [closed]
Is the best way to implement a large database is XML? Or is there a better way?
1 - reduce the time complexity
2 - Reduce the amount of traffic between the tables
And other factors ...
Which ...
6
votes
2answers
263 views
Adding a Clustered index to a HEAP table on someone else's app
We're using a proprietary application based on SQL Server 2005, which has many HEAP based tables (that is, no Clustered index). Over the years, these tables have grown badly fragmented (e.g. 99% ...
5
votes
3answers
162 views
Database Indexing - Maintenance Jobs
I have created a script that runs every night to rebuild & re-organize indexes based on the fragmentation, Indexes with Fragmentation > 30% are rebuilt, Indexes with Fragmentation 10% - 30% are ...
5
votes
1answer
250 views
Query performance while using sqlcmd [duplicate]
Possible Duplicate:
Running queries with SQLCMD vs. Running queries with SSMS
I have a number of scripts that query the data from different tables and consequently update them. I have ...
2
votes
1answer
468 views
LEFT JOIN / WHERE clause with is null and false Estimated Number Of Rows
I have a table named Product and a table named ProductPropertiesCountry.
Product is the definition of a product (description, price, productId, etc)
ProductPropertiesCountry contains restrictions on ...
3
votes
1answer
101 views
Analysing A Query Plan
Why is the following query slow?
select count(*)
from [dbo].[mt_dispatch_link]
, [dbo].[_mt_dispatch] [_mt_dispatch]
where (mt_dispatch_link.contract_id_1 = _mt_dispatch.contract_id
...
4
votes
2answers
889 views
Why is this query not using my nonclustered index, and how can I make it?
As follow up to this question about increasing query performance, I'd like to know if there is a way to make my index used by default.
This query runs in about 2.5 seconds:
SELECT TOP 1000 * FROM ...
9
votes
4answers
779 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
67 views
Optimizations for new SQL Server 2000 database that will act as email queue AND API database
Due to limitations put on me I've got to put my outgoing Email Queue in the same database as my customer information.
What are some optimizations I can make, specifically to the Email portion that ...
3
votes
1answer
95 views
can sql generate a good plan for this procedure?
I have read that procs that do not always do the same thing per se, will not always have a good plan generated. That is (correct me if I'm wrong), if I have a proc that if the day is even it reads ...
3
votes
1answer
89 views
How can I find out which statistics are evaluated by the optimizer?
My enterprise has a farm of many nearly-identical, isolated production environments. Each environment has a SQL Server 2005 database server, each supporting a database with nearly identical schemas. ...
5
votes
2answers
248 views
How to use merge hints to isolate complex queries in SQL Server
I can understand that if I join individual queries that are "individually fast" the combination may become slow because the default execution plan may be non-optimal. However when I know the number ...
1
vote
2answers
141 views
Explicitly update a table after sp_tableoption “text in row” advices wanted
I have an extremely slow sql server 2000 DB with 8GB, and the cause of this is the 3 ntext columns which are in the more queried table. Table has indexes set and also the full-text ssearch index set ...
2
votes
2answers
77 views
Performing a “Spread Negitive” over a dataset
I have a situation where a table that represents outstanding balances and credits are both contained in one table. What I need to do is apply all outstanding credits (preferably in order of oldest ...
3
votes
1answer
187 views
Optimizing queries against a set of tables that contain partitioned data
I have a third party program that creates and populates a series of tables to hold some historical data:
data_33 -- contains data for 12/01/11 - 12/05/11
data_34 -- contains data for 12/05/11 - ...
5
votes
2answers
1k views
REBUILD - Clustered Index, TABLE, or both?
I'm having trouble finding a definitive resource on this anywhere, so hopefully a guru can give me an answer here.
I have a very large table that we had to add a column to. The clustered index is ...
1
vote
2answers
390 views
Upper or Lower Case Optimisation on MS SQL Server
Does anyone know if MS SQL Server text queries are optimised for upper or lower case strings?
I read somewhere the it was uppercase, but cant seem to find the reference to it again. Of course if ...
8
votes
1answer
663 views
Monumental difference in execution time between queries when using RECOMPILE query hint
I have two almost identical queries running on the same SQL Server 2005 instance:
The first one is the original SELECT query as generated by LINQ (I know, I know... I'm not the application ...
5
votes
2answers
546 views
Speed up INSERTs
I need to have a way to insert to a table, fast, synchronous, with minimal duration. What I've tried ("blind") is :
Have no index on the table whatsoever
Switch to simple logging (from full logging)
...
1
vote
1answer
269 views
Optimization: Delete with Top and minimize the where clause date range
I have a delete statement that get run repetitive until all the data are removed.
Delete TOP (1000) From Log Where CreationDate < '2011-Dec-31' AND pKey NOT IN (...)
I am considering of ...
6
votes
1answer
144 views
On-the-fly query/command modification
I'm responsible for administrating an instance of SQL Server 200x which has the misfortune of serving as a backend to a turnkey application written with a disgusting performance problem. It repeatedly ...
9
votes
6answers
1k views
Suddenly Slow Execution Plan for Stored Proc
I'm trying to understand an issue we're having with SQL Server 2000. We are a moderately transactional website and we have a stored proc called sp_GetCurrentTransactions which accepts a customerID, ...
8
votes
1answer
787 views
Why is Clustered Index Scan Number of Executions so high?
I have two similar queries that generate the same query plan, except that one query plan executes a Clustered Index Scan 1316 times, while the other executes it 1 time.
The only difference between ...
4
votes
4answers
249 views
SQL Server Optimization/Configuration for Inflexible Application
I'm responsible for overseeing an application which uses SQL Server as its backend storage solution. Unfortunately, the application has no concept of how a RDBMS should be utilized and seems to use it ...
5
votes
2answers
283 views
How recording of every change of a row in a database is generaly stored?
In a project I'm working on, every change to the rows in some tables of the database must be tracked for further audit or rollback. It must be easy to find who modified the row, from which IP address ...
8
votes
2answers
493 views
Optimization: Moving variable declarations to the top of your procedure
While working on optimizing some stored procedures, I sat down with the DBA and went through some sprocs with high blocking and/or high read/write activity.
One thing the DBA mentioned was I should ...
5
votes
3answers
392 views
Does SQL server optimize or pre-parse stored procedures?
Despite what DBAs will tell you ("use stored procedures because the server will optimize them thus they are faster than ad-hoc queries"), I have heard that SQL server does not actually optimize stored ...
9
votes
4answers
5k views
Logical operators OR AND in condition and order of conditions in WHERE
Let's examine these two statements:
IF (CONDITION 1) OR (CONDITION 2)
...
IF (CONDITION 3) AND (CONDITION 4)
...
If CONDITION 1 is TRUE, will CONDITION 2 be checked?
If CONDITION 3 is FALSE, will ...
5
votes
5answers
400 views
Does SQL Sentry Plan Explorer work?
Does SQL Sentry Plan Explorer work as advertised and is it legitimate? Are there any gotchas or something to be concerned about?
It looks like it shows the hot path in color commpared to SSMS's ...
3
votes
2answers
256 views
How do I calculate working business minutes?
I'm working on business inteligence reports and one of my task is to create reports for time tracking.
I have to report on business minutes (10 hours per day) within business week (monday to ...
4
votes
2answers
580 views
SQL Server Wildcardless LIKE Optimization
In a web application using SQL Server 2008 R2 as the backend, I have several fields, that if present, add WHERE clauses to the query.
If I was to have all queries generated as like clauses, such as:
...
0
votes
2answers
505 views
How can I truly DELETE data from a SQL Server table - still shows up in notepad in backup file?
SQL Server DELETE only marks as DELETED though leaves as data in the data pages.
Data pages are 1024 KB in size and this problem can be eliminated by adding a FILLFACTOR that ensures only 1 record ...
9
votes
5answers
1k views
How do I query this 20 million record view faster?
For a search functionality I am using a view that has the records from all the tables within which I need to search for. The view has almost 20 million records. Searches against this view are taking ...
39
votes
6answers
8k views
How to determine if an Index is required or necessary
I've been running an auto-index tool on our MS SQL database (I modified a script originating from Microsoft that looks at the index statistics tables - Automated Auto Indexing). From the stats, I now ...
