Sp_executesql From App's Dynamic SQL Causing Performance Problems Vs. Query From SSMS

Aug 12, 2006

Okay, so I came across an odd performance issue that I'm wondering if some guru can help me out with.

I have a query that uses a paging algorithm that uses a paging algorithm and a table variable, then gets a page of data based on a join to that table variable. Here's a simplified query using the algoritm:

--declare table variable... not shown for brevity

--make sure we only store the least amount of records possible
SET ROWCOUNT ( @pageNumber + 1 ) * @pageSize

--insert into table variable
INSERT INTO @TableVariable( Key )
SELECT key FROM table
WHERE whatever = @p1

--we only want one page of data
SET ROWCOUNT @pageSize

--now get the page of data from the table
SELECT key FROM table
WHERE whatever = @p1
AND [TableVar Identity Column] > @pageNumber * @pageSize

The algorithm works great for our needs, BUT, I noticed something a little odd about its behavior during performance testing.

In particular, when I run the query using Sql Server Management Studio, where I manually DECLARE all the variables it ends up needing only 156 reads to complete the job. When I call it from the app using ADO.NET, however, I noticed it needs 310 reads! Huh?

I looked for differences, and the only one I could determine was that ADO.NET passes the query and uses sp_executesql and passes the parameters vs. declaring and setting them statically before executing the query. I confirmed that this was the issue by manually running sp_execute SQL and seeing that it took roughly the same number of reads (274) to process the query.

Naturally, I don't want the time it takes to perfrom my query to double, but and frankly I don't understand why there would be a difference in performance. Can anyone help me track down what is going on and suggest to me how to fix the problem.

I assume that SQL Server Management Studio optimizes the execution path somehow, but I'm not sure how to gain the same benefit for my passed query. Can I enable something with hints? Is there something else going on that I should know about?

View 10 Replies


ADVERTISEMENT

Build Dynamic Query Using Sp_executesql

Sep 20, 2004

Hi there,

I am trying to build a proc that uses a loop to import data into several tables. The data is copied into the appropriate table according to the contents of the variable @PracticeCode. I am also trying to add a date value to each record as it is added to the table. I thought that the best way to do this would be t use the sp_executesql stored proc. but I am having difficulty getting it to work. Here's what I have done so far:

-- insert data into proper tables with extract date added
SET @SQLString ='INSERT INTO GMS_48hrAccess.dbo.tbl_Surgery'+@PracticeCode+' SELECT
SurgeryKey,'+
@extractDate+',
ClinicianCode,
StartTime,
SessionGroup,
[Description],
SurgeryName,
Deleted,
PremisesKey
FROM GMS_48hrAccess.dbo.tbl_SurgeryIn'

EXEC master..sp_executesql @SQLString

And here's the error message that I get:

Server: Msg 241, Level 16, State 1, Line 90
Syntax error converting datetime from character string.

I understand why I am getting this error I just can't seem to fix it. I've consulted BOl and have tried various Parameter combinations but to no avail.

Can anyone help?

Thanks

View 1 Replies View Related

Performance Difference Between Query Executed Through ASP.NET And SSMS

Sep 18, 2007

I have also posted this in microsoft.public.sqlserver.programming.

I have a query which, depending on where I run it from, will either take 10 milliseconds or 10 seconds.

The query works perfectly when run in SQL Server Management Studio... in my database of around 70,000 items it returns the results in around 10ms. It uses all my indexes and indexed views correctly.

However when I run the identical query from my ASP.NET application, it takes around 10 seconds... 1000 times longer.
Looking at it in Sql Server Profiler I can't see any difference in the query, except from ASP.NET it needs 62531 reads and from SSMS it needs only 318 reads. If I copy the slow running ASP.NET query from the profiler into SSMS, then it runs quick again. The results returned are the same.

I have provided more details of the query below, but I guess my real question is: What is the best way to debug this? I'm not an expert with SQL Server, so any pointers on where I should start looking to find the difference in how the query is being executed would be a great help.

The query is of the form:

WITH RowPost AS
(
SELECT
ROW_NUMBER() OVER(ORDER BY DateCreated DESC) AS Row,
ItemId,
Title,
....
FROM
Items_View WITH(NOEXPAND)
WHERE ItemX >= @minX AND ItemX <= @maxX AND ItemY >= @minY AND ItemY <= @maxY
)
SELECT
*,
(SELECT Count(*) FROM RowPost) AS [Count]
FROM RowPost
WHERE Row >= @minRow AND Row < @maxRow

Where Items_View is an indexed view, and WITH(NOEXPAND) is being used to force it to use the indexed view (this is optimal). The line beginning "SELECT Count(*)" is to get the total number of results (without having to run the inner query a second time).

This is running against SQL Server Developer Edition.

View 5 Replies View Related

Exec Sp_executesql Vs. Sp_executesql And Performance

Jul 23, 2005

This is a odd problem where a bad plan was chosen again and again, butthen not.Using the profiler, I identified an application-issued statement thatperformed poorly. It took this form:exec sp_executesql N'SELECT col1, col2 FROM t1 WHERE (t2= @Parm1)',N'@Parm1 int', @Parm1 = 8609t2 is a foreign key column, and is indexed.I took the statement into query analyzer and executed it there. Thequery plan showed that it was doing a scan of the primary key index,which is clustered. That's a bad choice.I then fiddled with it to see what would result in a good plan.1) I changed it to hard code the query value (but with the parmdefinition still in place. )It performed well, using the correct index.Here's how it looked.exec sp_executesql N'SELECT cbord.cbo1013p_AZItemElement.AZEl_Intid AS[Oid], cbord.cbo1013p_AZItemElement.incomplete_flag AS [IsIncomplete],cbord.cbo1013p_AZItemElement.traceflag AS [IsTraceAmount],cbord.cbo1013p_AZItemElement.standardqty AS [StandardAmount],cbord.cbo1013p_AZItemElement.Uitem_intid AS [NutritionItemOid],cbord.cbo1013p_AZItemElement.AZeldef_intid AS [AnalysisElementOid] FROMcbord.cbo1013p_AZItemElement WHERE (Uitem_intid= 8609)', N'@Parm1 int',@Parm1 = 8609After doing this, re-executing the original form still gave badresults.2) I restored the use of the parm, but removed the 'exec' from thestart.It performed well.After that (surprise!) it also performed well in the original form.What's going on here?

View 3 Replies View Related

Sp_executesql Performance

Sep 20, 2006

Hi All,

The following is cut from SQL profiler, database is SQL 2000 SP4. Query 1 takes almost zero time and 26 reads. Query 2 takes 16 millsecs and 2862 reads and the only difference is that Query2 has parameters. I have run the query's multiple times and in different order and the results are the same. My reading of the documentation says that Query 2 should be faster due to not having to recreate the execution plan. If the execution plan is a bad one and is cached how do I remove it. Is there anyway to force a recompile or am I know in stored procedure territory.

Can anyone give me an explanation as it looks like we should be changing our code to use literal's where ever possible rather than parameter substitution.

TIA

SQL:BatchCompleted exec sp_executesql N'select *

from PA_REC_RECEIPT_ALLOCATIONS

where RECEIPT_PREFIX = ''LMH''

and RECEIPT_SUFFIX = 10652

' Microsoft SQL Server Management Studio - Query 0 26 0 0 3256 51 2006-09-20 13:15:32.843

-- Query 2

SQL:BatchCompleted exec sp_executesql N'select *

from PA_REC_RECEIPT_ALLOCATIONS

where RECEIPT_PREFIX = @P1

and RECEIPT_SUFFIX = @P2

', N'@P1 varchar(3),@P2 int', 'LMH', 10652

Microsoft SQL Server Management Studio - Query 16 2862 0 13 3256 51 2006-09-20 13:15:35.830

View 5 Replies View Related

Activating Dynamic Sql Whereby Sp_executesql

Apr 2, 2008

Hi everyone,
The following code to run dynamic sql:

DECLARE @params NVARCHAR(4000)
DECLARE @portion INT
SET @portion=6
DECLARE @mydynamic NVARCHAR(4000)
SELECT @mydynamic = ' SELECT TOP @portion * FROM server.databse.dbo.table'
SELECT @params = N'@portion INT '
EXEC sp_executesql @mydynamic,@params, @portion

yields the following error message:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '@portion'.

anyone has any idea why ? How it should be corrected?
Thanks

View 11 Replies View Related

SP_EXECUTESQL / Dynamic TSQL

Sep 4, 2007

Here is the query..
@ENTITY, @ FIELD, @KEYID, @VALUE comes dynamically using cursor. Here in this example I have took one sample and assigned it a value to do sanity check.



DECLARE @ENTITY nvarchar (100)

SET @ENTITY ='AccidentDimension'


DECLARE @FIELD nvarchar (100)

SET @FIELD = 'UHReceivedDate'


DECLARE @KEYID nvarchar (100)

SET @KEYID = '1074958'


DECLARE @VALUE varchar (100)

SET @VALUE = '10JAN2020'


DECLARE @FLAG NVARCHAR(50);

SET @FLAG = (SELECT LEFT(@ENTITY, (SELECT CHARINDEX( 'DIM', @ENTITY) -1)) )+ 'LastUpdateFlag';


DECLARE @KeyName NVARCHAR(50);

SET @KeyName = (SELECT LEFT(@ENTITY, (SELECT CHARINDEX( 'DIM', @ENTITY) -1)) )+ 'Key'



DECLARE @KeyValue INT

DECLARE @SQL1 NVARCHAR (1000)

SET @SQL1 = ' SET @KeyValueOUT = Select '+ @KeyName + ' FROM ClaimManagementFact WHERE ClaimKey = ' + @KEYID +

' GROUP BY ' + @KeyName + ' HAVING SUM(TotalClaimCount) > 0 OR SUM(IncidentOnlyClaimCount) > 0 )'


EXECUTE sp_executesql @SQL1, @KeyValueOUT INT OUTPUT;


@KeyValue= @KeyValueOUT OUTPUT;


Select @KeyValue

A) What i want to do is store the value resulting from select statemenet by executing @SQL1 which is INT to @KeyValue. In previous thread I tried various thing but resulting in errors.

Thanks in advance for help

View 6 Replies View Related

Exec Or Sp_Executesql Performance

Sep 11, 2001

I need to do some dynamic execs based on SQL strings as variables taken from a table.

I need the best performance. In testing, I'm finding that the string I am executing is taking 20 times as long to run from within an Exec or sp_executesql vs running the same string from directly within query analyzer. Of course, there's some overehead with Exec, and we're talking milliseconds (30ms in query analyzer vs 600 in an Exec), but this still seems like a lot of overhead. I assume both statements still need to be compiled and an execution plan created. Is this difference in time normal?

I ask because I'm debating about whether to execute this process (which may need to run 9000 times a second) in a stored procedure or using an executable running separately on the server. I assume an executable's performance will approximate running the same query from query analyzer.

Here's the query:

Select GroupNodeNumber, SB.* From (Select Distinct GroupNodeNumber from #QASData) RD Cross Join (SELECT * From SQL_QAS_ServiceFailure WHERE QASEventID=99077223) SB Where (GroupNodeNumber=10 and (((VerbalPOD=-1) or (Canceled=-1) or ((DeliveryRequirement) = -1) or
(DateDiff(n,GetDate(),DeliveryRequirement)-(DestTimeAdjustment)>240) or (InvoiceType<>0)))) or
(GroupNodeNumber=20 and ((Canceled=0) and (NOT (DeliveryRequirement) = -1) and (DateDiff(n,GetDate(),DeliveryRequirement)-(DestTimeAdjustment)>240) and (VerbalPOD=0) and (InvoiceType=0))) or
(GroupNodeNumber=30 and ((Canceled=0) and (NOT (DeliveryRequirement) = -1) and (DateDiff(n,GetDate(),DeliveryRequirement)-(DestTimeAdjustment)>120) and (DateDiff(n,GetDate(),DeliveryRequirement)-(DestTimeAdjustment)<=240) and (VerbalPOD=0) and (InvoiceType=0))) or
(GroupNodeNumber=40 and ((Canceled=0) and (InvoiceType=0) and (VerbalPOD=0) and (NOT (DeliveryRequirement) = -1) and (DateDiff(n,GetDate(),DeliveryRequirement)-(DestTimeAdjustment)<=120))) or
(GroupNodeNumber=50 and ((ShipmentStatus='POD') and (VerbalPOD=0) and (InvoiceType=0)))

View 1 Replies View Related

Dynamic Code Activated By Sp_executesql Can Anyone Help Me With That One?

Apr 2, 2008

Hi everyone
I try to run Dynamic sql wherby sp_executesql as follows:



Code Snippet
DECLARE @params NVARCHAR(4000)
DECLARE @portion INT
SET @portion=6
DECLARE @mydynamic NVARCHAR(4000)
SELECT @mydynamic = ' SELECT TOP @portion * FROM server.database.dbo.mytable'
SELECT @params = N'@portion INT '
EXEC sp_executesql @mydynamic,@params, @portion






I get the following error message:



Code Snippet
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '@portion'.




Any idea what is wrong with that code?Thanks

View 6 Replies View Related

Dynamic SQL Sp_executesql And Employee ID List Varchar

Jan 11, 2008

For the example stored procedure below, lets say I want to use the "sp_executesql" stored procedure instead of "EXECUTE".
 CREATE PROCEDURE [dbo].[spGetEmployees]
@managerId int,
@employeeIdList nvarchar( 200 )
AS

EXECUTE
(
'SELECT *
FROM [dbo].[hrEmployees]
WHERE [ManagerID] = ' + CAST( @managerId AS nvarchar ) + '
AND [EmployeeID] IN (' + @employeeIdList + ')'
)
 
 
I want to rewrite it something like this. Please see MSDN documentation ( http://msdn2.microsoft.com/en-us/library/ms188001.aspx ) for sp_executesql stored procedure usage.DECLARE @selectStatement nvarchar(500)
SET @selectStatement = 'SELECT * FROM [dbo].[hrEmployees] WHERE [ManagerID] = @paramManagerID AND [EmployeeID] IN (' + @employeeIdList + ')'


DECLARE @paramList nvarchar(500)
SET @paramList = '@paramManagerID int'

EXECUTE sp_executesql @selectStatement, @paramList, @paramManagerID = @managerId
 
Reason for using "sp_executesql" is the performance gain.However, as you can see, the @employeeIdList cannot be included as part of the Parameter List ( @paramList )like the @managerId since it **has** to be passed in as a varchar ( example: @employeeIdList = '1,2,3,4' ).
My Question Is there a way to include it as a parameter instead of it being part of the embedded dynamic SQL syntax?

View 1 Replies View Related

Strange Performance Problem With SELECT COUNT(1) And Sp_executesql

Feb 22, 2008

Hello,

I have the following queries that run on a view called EntrySummary:

1)

exec sp_executesql N'SELECT COUNT (1) FROM [dbo].[EntrySummary] WHERE [EntrySummary].[SubmissionStatusID] = @SubmissionStatusID0 AND [EntrySummary].[CreatedBy] = @CreatedBy1',N'@SubmissionStatusID0 int,@CreatedBy1 nvarchar(20)',@SubmissionStatusID0=4,@CreatedBy1=N'domainaperson'


2)
exec sp_executesql N'SELECT COUNT (1) FROM [dbo].[EntrySummary] WHERE [EntrySummary].[CreatedBy] = @CreatedBy1 AND [EntrySummary].[SubmissionStatusID] = @SubmissionStatusID0',N'@SubmissionStatusID0 int,@CreatedBy1 nvarchar(20)',@SubmissionStatusID0=4,@CreatedBy1=N'domainaperson'


(The only difference between the two queries being the order of the where clauses)

Both return the correct answer (4144), but Query 2 takes 10-15 seconds whereas Query 1 takes < second.
If the same query is resubmitted several times, this doesn't affect the times.

(The vast majority of the records have a status of 4, but only about 3% will be created by the person).

Replacing Count(1) with count(*) makes both queries return quickly.

Is sp_executesql creating poor execution plans ? Can anyone explain this behaviour?


Regards,
AndyM

View 1 Replies View Related

Transact SQL :: SSMS Pivot With Dynamic Columns

Apr 23, 2015

I found this Microsoft article for creating crosstab-like queries in SSMS.Is it possible, however, to create this same query if I do not know what the values for the columns will be?  Using their example for my problem, I will not know what the values in the "IN" criteria will be because my query would be for a "rolling" 12 months (thus causing that IN criteria to change every month).I've tried declaring variables to pull in the values, but since this will eventually go into a view, I don't think that I can use declared variables.

View 3 Replies View Related

SQL Query Assistance. MAX Causing Issue

May 23, 2007

I got some help on here before with building my query. I thought this was working fine but it turns out when there are multiple records for a column type, it only grabs the first one. I need to get all records. Is there an alternative to MAX? I needed to structure it like this because I needed to return each row as a column and this was the way suggessted before.
My query:SELECT TOP (100) PERCENT PRODUCT_NUMBER, PRODUCT_NAME,
MAX(CASE WHEN ColumnName = 'Federal Specification Number' THEN TheValue ELSE NULL END) AS [Federal Specification Number]FROM (SELECT dbo.PRODUCT_FEATURE_VALUES.PRODUCT_ID AS ProductID, dbo.SHARED_FEATURE_VALUES.FEATURE_TEXT_VALUE AS TheValue,
dbo.SHARED_FEATURE_TYPES.FEATURE_TYPE AS ColumnName, dbo.PRODUCTS.PRODUCT_NUMBER, dbo.PRODUCTS.PRODUCT_NAME
FROM dbo.PRODUCT_FEATURE_VALUES INNER JOINdbo.SHARED_FEATURE_TYPES ON
dbo.PRODUCT_FEATURE_VALUES.FEATURE_TYPE_ID = dbo.SHARED_FEATURE_TYPES.FEATURE_TYPE_ID INNER JOINdbo.SHARED_FEATURE_VALUES ON
dbo.PRODUCT_FEATURE_VALUES.FEATURE_VALUE_ID = dbo.SHARED_FEATURE_VALUES.FEATURE_VALUE_ID INNER JOINdbo.PRODUCTS ON dbo.PRODUCT_FEATURE_VALUES.PRODUCT_ID = dbo.PRODUCTS.PRODUCT_ID
UNIONSELECT dbo.EXTENDED_ATTRIBUTE_VALUES.PRODUCT_ID AS ProductID, ISNULL(dbo.EXTENDED_ATTRIBUTE_VALUES.SMALL_TEXT_VALUE,
dbo.EXTENDED_ATTRIBUTE_VALUES.LARGE_TEXT_VALUE) AS TheValue, dbo.EXTENDED_ATTRIBUTES.COLUMN_NAME AS ColumnName, PRODUCTS_1.PRODUCT_NUMBER, PRODUCTS_1.PRODUCT_NAME
FROM dbo.EXTENDED_ATTRIBUTE_VALUES INNER JOINdbo.EXTENDED_ATTRIBUTES ON
dbo.EXTENDED_ATTRIBUTE_VALUES.EXT_ATT_ID = dbo.EXTENDED_ATTRIBUTES.EXT_ATT_ID INNER JOIN
dbo.PRODUCTS AS PRODUCTS_1 ON dbo.EXTENDED_ATTRIBUTE_VALUES.PRODUCT_ID = PRODUCTS_1.PRODUCT_ID) AS t1
WHERE PRODUCT_NUMBER = '02083'
GROUP BY PRODUCT_NUMBER, PRODUCT_NAME
ORDER BY PRODUCT_NUMBER
 
This returns:
Product_Number    Product_Name                                 Federal Specification Number 
02083                   Di-Electric Grease, 10.5 Wt Oz          FDZ-CFR-21-178.3570
There is another record for Federal Specification Number I need to return as well. If I change to MIN, it gets the other record. Anyway I can get both?

View 15 Replies View Related

Simple Query Causing Frustration

May 27, 2008

Hi, I am a new learner and user to SQL Sever 2005 and am having some major frustration trying to write a simple query.

I have two tables, 1) Ticket_Purchase, 2) Flight.

The Ticket_Purchase table has these columns: Ticket_Purchase_Number(PK), Flight_Number(FK), Date_Purchase_Made, Ticket_Price, Class_of_Ticket, Passenger_ID

The Flight table has these columns: Flight_Number(PK), Flight_Date, Flight_Departure_Time, Flight_Arrival_Time, Flight_Origin, Flight_Destination

I am trying to create a query that will tell me: On which flight were the most first class tickets sold?

There are only two types of classes; 'E' for economy and 'F' for First Class.

So far I am able to get a list of all the First class flights for each flight and can visually see which flight has the most first class tickets by counting them manually on the report generated, but I am totally confused on how to simply pull the single flight with the most First class tickets sold. I wonder if this requires something more like a join or a nested sub query?

The SQL I wrote for the above is:

Select Class_of_Ticket, Flight_Number
From Ticket_Purchase
Where Class_of_Ticket = ('F')
Order By Flight_Number;


And it produces:

Class_of_Ticket Flight_Number
--------------- -------------
F 1
F 1
F 1
F 2
F 2
F 3
F 3
F 3
F 3
F 4
F 4
F 4
F 4
F 4
F 4
F 4
F 4
F 4
F 4
F 4
F 4
F 4
F 4
F 5
F 5
F 6
F 6
F 6
F 7
F 7
F 8
F 8
F 8
F 9
F 9
F 9
F 9
F 9

(38 row(s) affected)



Rather I would like it to produce:
First_Class_Seats Purchased Flight_Number
--------------------------- ------------
14 4


I hope I didn't make this to confusing to understand as I am still learning the syntax and 'lingo' of how to communicate this stuff verbally.

Thank you for any help you could offer. It would be much appreciated.

Edit: the query report I pasted from SQL should have the flight number directly under the column header. For some reason the space between Class and Flight_number is being eliminated in the post.

View 5 Replies View Related

SQL Server CE Update Query Causing Errors

Jan 14, 2005

Hello all,

Thought I would post here in case anybody can give some information.

Here is the background information:

I have 2 tables (stores and sales) from the Pubs database in Sql Server 2000 copied down to a SQL Server CE database. There is no foreign key/primary key relationship between the 2 tables in the CE database.

Here are the update queries that cause the error:

UPDATE st
SET st.zip = 66668
FROM stores st
INNER JOIN sales sa ON st.stor_id = sa.stor_id
AND st.stor_id = 6380

Update stores SET stores.zip = 55555
FROM sales, stores
WHERE stores.stor_id = 6380
AND stores.stor_id = sales.stor_id

Here is the error message that is generated when I run the query (Param 0 and Param 1 change according to what column and line the FROM clause is in):

Error: 0x80040e14 DB_E_ERRORSINCOMMAND
Native Error: (25501)
Description: There was an error parsing the query. [Token line number,Token line offset,,Token in error,,]
Interface defining error: IID_ICommand
Param. 0: 2
Param. 1: 1
Param. 2: 0
Param. 3: FROM
Param. 4:
Param. 5:

I ran the 2 queries in SQL Query Analyzer in SQL Server 2000 and they worked just fine. I also created 2 new tables (stores1 and sales1) in SQL Server 2000 using the Select Into clause. The new tables were created from the sales table and stores table in the Pubs database. The new tables had no foreign key/primary key relationship.

I ran the queries again in Query Analyzer against the new tables and the queries produced no errors.

Any suggestions?

Thank you,
Aaron B

View 1 Replies View Related

Simple Query Causing Strange Problem.

Feb 11, 2004

declare @l decimal(38,2)
select @l = 24.35

if @l - convert(int,@l) = 0
select floor (@l)
else
select @l

select case
when @l - convert(int, @l) = 0 then floor (@l)
else @l
end

The if statement is giving correct result, but the case statement is not. I am fed up why it is so. Please advise.

View 14 Replies View Related

Problem Running SP_EXECUTESQL With OpenRowSet (with Variable) As Part Of The Query

Nov 27, 2007

Hi,

I had problem when combining OpenRowSet and SP_EXECUTESQL, when i tried to run the following query, it complaints that RESID is not declared. any idea how should i put the query so i will pass @RESID as 1 of the parameter? BTW, i know that the SP_EXECUTESQL is able to run query which length up to 8000, but how about the parameter?


DECLARE @SqlToRun NVARCHAR(MAX)
DECLARE @RESOURCEIDS NVARCHAR(MAX)

SET @RESOURCEIDS = REPLICATE(CAST('ABC' AS NVARCHAR(MAX)), 1000)

SET @SqlToRun = N'SELECT * FROM
OPENROWSET(''SQLNCLI'',
''server=;database=;uid=;pwd='',
''SELECT * FROM WHERE COL=@RESID'')'

DECLARE @PARAMS NVARCHAR(MAX)
SET @PARAMS = ''@RESID NVARCHAR(MAX)'

EXECUTE SP_EXECUTESQL @SqlToRun, @PARAMS, @RESID = @RESOURCEIDS

Regards,
Derek

View 5 Replies View Related

SQL Server 2014 :: Query Causing Blocking / Locks Table For 1000 Seconds?

Feb 11, 2015

I have a sql snippet from a 3rd party application that will not complete its transaction. The SELECT statement executes but does not finish. Instead the statement just sits in AWAITING COMMAND for 1000 seconds then dies, thus killing the UPDATE statement that is supposed to follow.

The CROSS JOIN and CROSS APPLY seem suspect.

(
@p0 DATETIME,
@p1 INT,
@p2 INT,
@p3 NVARCHAR(4000),
@p4 INT,

[code]....

View 9 Replies View Related

SQL Server Admin 2014 :: SPID Causing Locks With No Query Text Shown

Oct 1, 2015

I just ran into an issue with cascading locks due to a SPID on one of my production servers. When researching the lock, I noticed that there was no sql text. SP_Who 2, nor the following query captured anything,

SELECT sqltext.TEXT
, sqlplan.query_plan
, req.session_id
, req.status
, req.command

[Code] ....

I spoke to the user causing the lock and he ran into a visual basic error when this occurred and didn't close out that window. So my guess is that it's due to an uncommitted transaction. However, shouldn't I still see something if that was the case?

View 4 Replies View Related

Extremely Poor Query Performance - Identical DBs Different Performance

Jun 23, 2006

Hello Everyone,I have a very complex performance issue with our production database.Here's the scenario. We have a production webserver server and adevelopment web server. Both are running SQL Server 2000.I encounted various performance issues with the production server with aparticular query. It would take approximately 22 seconds to return 100rows, thats about 0.22 seconds per row. Note: I ran the query in singleuser mode. So I tested the query on the Development server by taking abackup (.dmp) of the database and moving it onto the dev server. I ranthe same query and found that it ran in less than a second.I took a look at the query execution plan and I found that they we'rethe exact same in both cases.Then I took a look at the various index's, and again I found nodifferences in the table indices.If both databases are identical, I'm assumeing that the issue is relatedto some external hardware issue like: disk space, memory etc. Or couldit be OS software related issues, like service packs, SQL Serverconfiguations etc.Here's what I've done to rule out some obvious hardware issues on theprod server:1. Moved all extraneous files to a secondary harddrive to free up spaceon the primary harddrive. There is 55gb's of free space on the disk.2. Applied SQL Server SP4 service packs3. Defragmented the primary harddrive4. Applied all Windows Server 2003 updatesHere is the prod servers system specs:2x Intel Xeon 2.67GHZTotal Physical Memory 2GB, Available Physical Memory 815MBWindows Server 2003 SE /w SP1Here is the dev serers system specs:2x Intel Xeon 2.80GHz2GB DDR2-SDRAMWindows Server 2003 SE /w SP1I'm not sure what else to do, the query performance is an order ofmagnitude difference and I can't explain it. To me its is a hardware oroperating system related issue.Any Ideas would help me greatly!Thanks,Brian T*** Sent via Developersdex http://www.developersdex.com ***

View 2 Replies View Related

Query Replica Directly Via SSMS?

Apr 3, 2015

I know it is possible to use the secondary replica to pull data (application intent) etc..

But is there some way to query the replica directly via SSMS?

View 2 Replies View Related

No Available Databases In A New Query In SSMS 2005

Mar 7, 2007

When I create a new query window in SSMS 2005 (SP2) , I do not have any available database in de SQL Editor pane. Though in the Object Explorer window below, I can access all objects of all databases. The only way to query a table is by using the 'Open Table' feature in the Object Explorer window, then right-click on Pane-->SQL .

I also can open a stored procedure, but not modify it as the SQL Editor pane is disabled.

The SQL-Editor pane is available for all the other servertypes (Analysis, Reporting, Integration).

All services all started.

I re-installed completely SQL2005 and SP2 : nothing changed.

What am I missing ???



Versions = Microsoft SQL Server Management Studio 9.00.3042.00

SQL Developer Edition 2005 (Service Pack 2)

Windows XP Pro 5.1 (build 2600 Service Pack 2)



Any help is welcome !!!

View 6 Replies View Related

Dynamic Dimension Security Limitations And Performance When Using Custom Dll (UDF)

May 21, 2008

Hi,

I've created a custom dll using ADOMD.net that takes in a few parameters (Username being one of them) and returns a set of Allowed Members to apply dynamic dimension security. Example:





Code Snippet

Security.GetSetList(UserName,1,"SQL Server")

This works fine for users with less than, say, 100 tuples/members in their set....however for users with more an error:

Error ocurred retreiving child nodes: The 'S' attribute in the 'S' dimension has a generated dimension security expression that is not valid. ...


However, if I take the same set and explicitly (ie


Code Snippet

{[S].[1],[S].[2],.......[S].[50000]}) put it in the Allowed set there's no issue!

I noticed that it takes the UDF a couple of seconds longer to return for users with a large security context. Is there a time-out parameter that controls this? Is there a limitation in the amount of members I can add via custom dll (UDF)? (Some users can have up to 70,000 members)

I should also mention that the UDF uses the StrtoSet function to convert the security context string to a set. The string can be as large as 210,000 characters. Is there a limitation on the string size that can be passed in this function?

Any assistance would be greatly appreciated.

Thanks!!

View 7 Replies View Related

SQL 2012 :: Pasting Query Results Into XL From SSMS

Mar 13, 2014

Previous to 2012, grid results from SSMS queries pasted into Excel beautifully and were easy to format.in 2008, The results from this query pasted into a spreadsheet with the query contained in a single Excel cell:

(sample - not the whole script)
select top 300
Avg_CPU_Time
,Total_Physical_Reads
,convert(datetime,Last_execution_time) as Timestamp
,Stored_Procedure
,Query_text
from dbadmin.dbo.History_CPU_IO_ByQueryAndSP

This was wonderful. I could deliver to developers wonderfully tidy reports on query resource usage with timestamps along with which stored procedure the queries came from.

Can't do that in SSMS 2012. Try it. It's a disaster. The Query_text, when pasted, spreads across multiple Excel columns, including the ones designated for other data. The result is totally unreadable.how to make the query_text stay in it's own cell? I've tried converting query_text to varchar and ntext. Same results.

View 9 Replies View Related

SQL Server 2012 :: Query Parses In SSMS But Not In SSIS?

Oct 20, 2015

The environment is Server 2008 32-bit, SQL 2012 SP2 Standard 32-bit (11.00.5058), and Visual Studio 10 SP1 (10.0.40219.1).

I have a query that begins with MERGE <tablename> USING (SELECT blah, blah, blah While in SSMS, I can parse the query with no issues. I can execute the query and see the results I expect. But when I put the same query into an Execute SQL Task in SSIS, it won't parse. It gives me one of those very informative messages that I so love - Query failed to parse. Incorrect syntax near the keyword "MERGE".

I don't know if the 32-bit/64-bit thing has any bearing or not, but I have taken the exact same project folder, copied it to a 64-bit box, and it works fine in both SSMS and SSIS. That one is running Server 2012 R2, SQL 2012 SP2 Standard (11.00.5343), and Visual Studio 10 SP1 (10.0.40219.1).

View 5 Replies View Related

SSMS Truncating Result Text In Select Query

Apr 20, 2015

The value of one of the columns in my table is 14000 lines(678913 characters). The datatype of that column is varchar(MAX). I am doing the following select query but its truncating the results.

select value -- this is truncating the text.
from dbo.GUISETTINGS

The length is shown as below when I do the query:

SELECT DATALENGTH(VALUE) from dbo.GUISETTINGS -- return 707951 as the length. 

I even tried running the query below but still the value is getting truncated. However, if I right-click and select "Save Results As" a file, then it shows all the lines/characters fine.

select value, cast(value as text), cast(value as varchar(max))
from dbo.GUISETTINGS

How can I get whole column value ?

View 17 Replies View Related

SQL Server 2008 :: SSMS Query Execution Messages History?

Sep 9, 2015

I would like to view the previous executed query error message in SSMS. Is there any way to view the query execution error messages history in SSMS?

View 2 Replies View Related

Copy Table Column Names From SSMS Object Browser To Use In A Query

Nov 6, 2007

I thought I saw this done once before. So today I hunted around inBooks OnLine and did a Google search. So far I have found nothingclose. So if you know how to do it, please tell me or if cannot bedone, I'd appreciate know that too.Thanks in advance,IanO

View 2 Replies View Related

DB Engine :: Cost Vs Time When Including Actual Query Plan In SSMS

Nov 11, 2015

I have two queries yielding the same result that I wanted to compare for performance. I did enter both queries in one Mangement Studio query window and execute them as one batch with the actual query plan included.Query 1 took 8.2 seconds to complete and the query plan said that the cost was 21% of the batchQuery 2 took 2.3 seconds to complete and the query plan said that the cost was 79% of the batch.The queries were run on my local development machine. I was the only user. No other programs were running at the time of this test. The results are repeatable.I understand that the query with the lowest cost is not necessarily the fastest query. On the other hand, the difference is quite big. The query that has approx. 80% of the cost takes 20% of the time and the other way around. I have two questions:

Is such a discrepancy normal?Can conclusions be drawn from the cost distribution? For instance, does the query that takes 8.2 seconds but only costs 21% scale better?

View 9 Replies View Related

SQL Tools :: Change SSMS 2012 Default Query Save Location?

Dec 2, 2013

is there a way to change SSMS 2012 default query save location?

Scenario:

1. Open SSMS
2. Create new query
3. Click Save

I see "DocumentsSQL Server Management Studio" folder, but I want to change it to be "d:". How do I do this?
I tried:

1. [URL]- in folder "DocumentsSQL Server Management StudioSettingsSQL Server Management Studio" there is a file NewSettings.vssettings, setting it to "d:" or "d:" didn't work.

2. Changing HKEY_CURRENT_USERSoftwareMicrosoftMicrosoft SQL Server100ToolsShell VisualStudioProjectsLocation didn't work too. There is no "Shell" under "110Tools"

Did I do something wrong, or is there another way?

View 6 Replies View Related

Dynamic Cursor Versus Forward Only Cursor Gives Poor Performance

Jul 20, 2005

Hello,I have a test database with table A containing 10,000 rows and a tableB containing 100,000 rows. Rows in B are "children" of rows in A -each row in A has 10 related rows in B (ie. B has a foreign key to A).Using ODBC I am executing the following loop 10,000 times, expressedbelow in pseudo-code:"select * from A order by a_pk option (fast 1)""fetch from A result set""select * from B where where fk_to_a = 'xxx' order by b_pk option(fast 1)""fetch from B result set" repeated 10 timesIn the above psueod-code 'xxx' is the primary key of the current Arow. NOTE: it is not a mistake that we are repeatedly doing the Aquery and retrieving only the first row.When the queries use fast-forward-only cursors this takes about 2.5minutes. When the queries use dynamic cursors this takes about 1 hour.Does anyone know why the dynamic cursor is killing performance?Because of the SQL Server ODBC driver it is not possible to havenested/multiple fast-forward-only cursors, hence I need to exploreother alternatives.I can only assume that a different query plan is getting constructedfor the dynamic cursor case versus the fast forward only cursor, but Ihave no way of finding out what that query plan is.All help appreciated.Kevin

View 1 Replies View Related

Dynamic (on The Fly) Query

Jun 20, 2006

Hi all.  I'm currently encountering a problem in attempting to find a solution for a dynamic or on the fly query.  I understand how you can make a static query and return it as a dataset; for example, say you have a field where a user enters a name on the front end and the db returns the results:
Dim queryString As String = "SELECT [Table].* FROM [Table] WHERE ("& _                                "[Table].[Name] = @Name)"
But what if I have multiple items I would like query based upon what the user picks.  For example, say you have five fields: Name, ID Number, Date, Address, and State.  Say the user wants to pick all data with a date between Jan. 06 to April 06, with the name of Tom, and in the state of CA.  But then next time, the user only wants all data with the name of Susan.  So the query is always changing and I am not sure exactly how to go about it.  I guess I sorta want similiar functionality as that of the Custom Auto Filter in Excel.  I've been reading a couple of the forums and I think people are using a string to pass to query the database.  But I am still vague on how to approach this.  Any help would be greatly appreciated!

View 5 Replies View Related

Dynamic Query

Sep 14, 2007

I am having trouble wrapping my head around some dynamic queries.  I have x number of queries stored in a table.  Each row in this table has a From, Join, and Where column.  So, for x=3, I can run the query from each row so that I may have
Q1:  (1, 2, 4, 5, 7, 8)
Q2:  (1, 3, 5, 7, 9)
Q3:  (2, 4, 6, 8, 10)
I need to use these queries to generate a shared table: |-------------------|
| ID | Q1 | Q2 | Q3 |
| 1 | 1 | 1 | 0 |
| 2 | 1 | 0 | 1 |
| 3 | 0 | 1 | 0 |
| 4 | 1 | 0 | 1 |
| 5 | 1 | 1 | 0 |
| 6 | 0 | 0 | 1 |
| 7 | 1 | 1 | 0 |
| 8 | 1 | 0 | 1 |
| 9 | 0 | 1 | 0 |
| 10 | 0 | 0 | 1 |
|-------------------| I'm not sure the best way to do this.  I think that doing it on the asp.net side will be easier than in t-sql, although I am exploring both possibilities.  Any suggestions?

View 13 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved