sql execution plan - Page 149

What tools are people using to measure SQL Server database performance?

- by Paul McLoughlin

I've experimented with a number of techniques for monitoring the health of our SQL Servers, ranging from using the Management Data Warehouse functionality built into SQL Server 2008, through other commercial products such as Confio Ignite 8 and also of course rolling my own solution using perfmon, performance counters and collecting of various information from the dynamic management views and functions. What I am finding is that whilst each of these approaches has its own associated strengths, they all have associated weaknesses too. I feel that to actually get people within the organisation to take the monitoring of SQL Server performance seriously whatever solution we roll out has to be very simple and quick to use, must provide some form of a dashboard, and the act of monitoring must have minimal impact on the production databases (and perhaps even more importantly, it must be possible to prove that this is the case). So I'm interested to hear what others are using for this task? Any recommendations?

Read the article

How does SQL Server treat statements inside stored procedures with respect to transactions?

- by Sleepless

Hi All! Say I have a stored procedure consisting of several seperate SELECT, INSERT, UPDATE and DELETE statements. There is no explicit BEGIN TRANS / COMMIT TRANS / ROLLBACK TRANS logic. How will SQL Server handle this stored procedure transaction-wise? Will there be an implicit connection for each statement? Or will there be one transaction for the stored procedure? Also, how could I have found this out on my own using T-SQL and / or SQL Server Management Studio? Thanks!

Read the article

How to figure the read/write ratio in Sql Server?

- by Bill Paetzke

How can I query the read/write ratio in Sql Server 2005? Are there any caveats I should be aware of? Perhaps it can be found in a DMV query, a standard report, a custom report (i.e the Performance Dashboard), or examining a Sql Profiler trace. I'm not sure exactly. Why do I care? I'm taking time to improve the performance of my web app's data layer. It deals with millions of records and thousands of users. One of the points I'm examining is database concurrency. Sql Server uses pessimistic concurrency by default--good for a write-heavy app. If my app is read-heavy, I might switch it to optimistic concurrency (isolation level: read uncommitted snapshot) like Jeff Atwood did with StackOverflow.

Read the article

Entity Framework VS LINQ to SQL VS ADO.NET with stored procedures?

- by BritishDeveloper

How would you rate each of them in terms of: Performance Speed of development Neat, intuitive, maintainable code Flexibility Overall I like my SQL and so have always been a die-hard fan of ADO.NET and stored procedures but I recently had a play with Linq to SQL and was blown away by how quickly I was writing out my DataAccess layer and have decided to spend some time really understanding either Linq to SQL or EF... or neither? I just want to check, that there isn't a great flaw in any of these technologies that would render my research time useless. E.g. performance is terrible, it's cool for simple apps but can only take you so far

Read the article

Performance optimization for SQL Server: decrease stored procedures execution time or unload the ser

- by tim

We have a web service which provides search over hotels. There is a problem with performance: a single request to the service takes around 5000 ms. Almost all of the time is spent in database by executing storing procedures. During the request our server (mssql2008) consumes ~90% of the processor time. When 2 requests are made in parallel the average time grows and is around 7000 ms. When number of request is increasing, the average time of response is increasing as well. We have 20-30 requests per minute. Which kind of optimization is the best in this case having in mind that the goal is to provide stable response time for the service: 1) Try to decrease the stored procedures execution time 2) Try to find the way how to unload the server It is interesting to hear from people who deal with booking sites.

Read the article

How can I save the schema of a SQL Database to a file?

- by Eric

I'm writing a software application in C#.Net that connects to a SQL Server database. My C# project is under SVN version control, but I'd like to include my database schema in the SVN repository as well. An answer to a previous question of mine suggested storing the scripts to generate the database in version control. Is there a way to automatically generate these scripts from an existing database? I'm very new to SQL Server, but I noticed in management studio that the SQL commands to create a table can be generated automatically by right clicking on the table and clicking "Script Table As". Is there an equivalent command that would work with the entire database?

Read the article

How do I get a count of events each day with SQL?

- by upl8

I have a table that looks like this: Timestamp Event User ================ ===== ===== 1/1/2010 1:00 PM 100 John 1/1/2010 1:00 PM 103 Mark 1/2/2010 2:00 PM 100 John 1/2/2010 2:05 PM 100 Bill 1/2/2010 2:10 PM 103 Frank I want to write a query that shows the events for each day and a count for those events. Something like: Date Event EventCount ======== ===== ========== 1/1/2010 100 1 1/1/2010 103 1 1/2/2010 100 2 1/2/2010 103 1 The database is SQL Server Compact, so it doesn't support all the features of the full SQL Server. The query I have written so far is SELECT DATEADD(dd, DATEDIFF(dd, 0, Timestamp), 0) as Date, Event, Count(Event) as EventCount FROM Log GROUP BY Timestamp, Event This almost works, but EventCount is always 1. How can I get SQL Server to return the correct counts? All fields are mandatory.

Read the article

need help with db-query on sql-server 2005.

- by Avinash

We're seeing strange behavior when running two versions of a query on SQL Server 2005: version A: SELECT otherattributes.* FROM listcontacts JOIN otherattributes ON listcontacts.contactId = otherattributes.contactId WHERE listcontacts.listid = 1234 ORDER BY name ASC version B: DECLARE @Id AS INT; SET @Id = 1234; SELECT otherattributes.* FROM listcontacts JOIN otherattributes ON listcontacts.contactId = otherattributes.contactId WHERE listcontacts.listid = @Id ORDER BY name ASC Both queries return 1000 rows; version A takes on average 15s; version B on average takes 4s. Could anyone help us understand the difference in execution times of these two versions of SQL? If we invoke this query via named parameters using NHibernate, we see the following query via SQL Server profiler: EXEC sp_executesql N'SELECT otherattributes.* FROM listcontacts JOIN otherattributes ON listcontacts.contactId = otherattributes.contactId WHERE listcontacts.listid = @id ORDER BY name ASC', N'@id INT', @id=1234; ...and this tends to perform as badly as version A. Thanks in advance,

Read the article

Is there a combination of "LIKE" and "IN" in SQL?

- by Techpriester

Hi folks. In SQL I (sadly) often have to use "LIKE" conditions due to databases that violate nearly every rule of normalization. I can't change that right now. But that's irrelevant to the question. Further, I often use conditions like WHERE something in (1,1,2,3,5,8,13,21) for better readability and flexibility of my SQL statements. Is there any possible way to combine these two things without writing complicated sub-selects? I want something as easy as WHERE something LIKE ('bla%', '%foo%', 'batz%') instead of WHERE something LIKE 'bla%' OR something LIKE '%foo%' OR something LIKE 'batz%' I'm working with MS SQl Server and Oracle here but I'm interested if this is possible in any RDBMS at all.

Read the article

How to modify the Title Bar text for SQL Server Management Studio?

- by DaveDev

Sometimes I keep multiple instances of SQL Server Management Studio 2005 open. I might have the dev database open in one, and the production database open in another. These appear in the Windows task bar with the text "Microsoft SQL Serve...", which means it's impossible to differentiate between them unless I open the window and scroll the Object Explorer up to see what server the window is actually connected to. Is ther any way that I can get the window to display the server name first, and then the name of the application? Like "Dev-DB.database_name - Microsoft SQL Serve..." or whatever?

Read the article

T-SQL - Is there a (free) way to compare data in two tables?

- by RPM1984

Okay so i have table a and table b. (SQL Server 2008) Both tables have the exact same schema. For the purposes of this question, consider table a = my local dev table, table b = the live table. I need to create a SQL script (containing UPDATE/DELETE/INSERT statements) that will update table b to be the same as table a. This script will then be deployed to the live database. Any free tools out there that can do this, or better yet a way i can do it myself? I'm thinking i probably need to do some type of a join on all the fields in the tables, then generate dynamic sql based on that. Anyone have any ideas?

Read the article

How to force SQL Server 2008 to not change AUTOINC_NEXT value when IDENTITY_INSERT is ON ?

- by evilek

Hello, I got question about IDENTITY_INSERT. When you change it to ON, SQL Server automatically changes AUTOINC_NEXT value to the last inserted value as identity. So if you got only one row with ID = 1 and insert row with ID = 100 while IDENTITY_INSERT is ON then next inserting row will have ID = 101. I'd like it to be 2 without need to reseed. Such behaviour already exists in SQL Server Compact 3.5. Is it possible to force SQL Server 2008 to not change AUTOINC_NEXT value while doing insert with IDENTITY_INSERT = ON ?

Read the article

Is it possible to password protect an SQL server database even from administrators of the server ?

- by imanabidi

I want to install an application (ASP.Net + SQL server 2005 express) in local network of some small company for demo but I also want nobody even sysadmin see anything direct from the database and any permission wants a secure pass . I need to spend more time on this article Database Encryption in SQL Server 2008 Enterprise Edition that i found from this answer is-it-possible-to-password-protect-an-sql-server-database but 1.I like to be sure and more clear on this because the other answer in this page says : Yes. you can protect it from everyone except the administrators of the server. 2.if this is possible, the db have to be enterprise edition ? 3.is there any other possible solutions and workaround for this? thanks in advance

Read the article

Why SQL Server Express 2008 install requires Visual Studio 2008 in checklist ?

- by asksuperuser

When installing SQL Server Express Edition 2008, checklist says "Previous version of Visual Studio 2008" and asked me to upgrade to sp1. Unfortunately sp1 for some reason refuses to install on my brand new pc (Windows 7). So why can't I just bypass this ? Why would SQL Server Express needs VS2008 to install that's insane. SQL Server install used to be as easy as 123, now it has become a nightmare like installing Oracle. Will I have to go back to Windows XP ?

Read the article

Linq to SQL DateTime values are local (Kind=Unspecified) - How do I make it UTC?

- by ericsson007

Isn't there a (simple) way to tell Linq To SQL classes that a particular DateTime property should be considered as UTC (i.e. having the Kind property of the DateTime type to be Utc by default), or is there a 'clean' workaround? The time zone on my app-server is not the same as the SQL 2005 Server (cannot change any), and none is UTC. When I persist a property of type DateTime to the dB I use the UTC value (so the value in the db column is UTC), but when I read the values back (using Linq To SQL) I get the .Kind property of the DateTime value to be 'Unspecified'. The problem is that when I 'convert' it to UTC it is 4 hours off. This also means that when it is serialized it it ends up on the client side with a 4 hour wrong offset (since it is serialized using the UTC).

Read the article

Beyond S&OP: Integrated Business Planning

- by Paul Homchick

In most corporations, planning is done at the department level — leaving disconnects and gaps across different departments. Finance sets revenue and profit goals with minimum validation from Manufacturing that the company has the resources, material, capacity, or demand to reach these goals. On the operations side, Manufacturing is developing plans to balance demand and supply but seldom knows if the resulting "plan" will meet the budgets on which the company's revenue and profit goals are based. The Sales department agrees to quotas that meet Finance's revenue goals without a complete understanding of what manufacturing can deliver. Integrated Business Planning (IBP) bridges these gaps in corporate planning systems. Integrated Business Planning integrates the financial planning provided by EPM systems with operations planning provided by Sales and Operations Planning solutions. This means that revenue goals and budgets are validated against a bottom-up operating plan, and that the operating plan is reconciled against financial goals. When detailed changes are made to the operations plan, planners can immediately see the big picture impact of the changes. IBP also addresses one the CFO's big concerns—the reliability of the revenue forecast. Operating plans are updated daily or weekly from a precise forecast based on current market conditions. These updated plans are then made available so that financial analysts are working with data that best represents what is going to happen - not what they projected would happen based on last quarter's data. For a discussion in more depth, see my article: Improve Reliability of Financial Forecasts with Integrated Business Planning in Supply & Demand Chain-Executive Magazine.

Read the article

Beyond S&OP: Integrated Business Planning

- by Paul Homchick

In most corporations, planning is done at the department level — leaving disconnects and gaps across different departments. Finance sets revenue and profit goals with minimum validation from Manufacturing that the company has the resources, material, capacity, or demand to reach these goals. On the operations side, Manufacturing is developing plans to balance demand and supply but seldom knows if the resulting "plan" will meet the budgets on which the company's revenue and profit goals are based. The Sales department agrees to quotas that meet Finance's revenue goals without a complete understanding of what manufacturing can deliver. Integrated Business Planning (IBP) bridges these gaps in corporate planning systems. Integrated Business Planning integrates the financial planning provided by EPM systems with operations planning provided by Sales and Operations Planning solutions. This means that revenue goals and budgets are validated against a bottom-up operating plan, and that the operating plan is reconciled against financial goals. When detailed changes are made to the operations plan, planners can immediately see the big picture impact of the changes. IBP also addresses one the CFO's big concerns—the reliability of the revenue forecast. Operating plans are updated daily or weekly from a precise forecast based on current market conditions. These updated plans are then made available so that financial analysts are working with data that best represents what is going to happen - not what they projected would happen based on last quarter's data. For a discussion in more depth, see my article: Improve Reliability of Financial Forecasts with Integrated Business Planning in Supply & Demand Chain-Executive Magazine.

Read the article

SSIS package randomly hangs during execution

- by Adam MacLeod

Hi Guys, I am having an ongoing and painful problem with an SSIS package. The package runs every 5 minutes as an SQL Agent Job and every 2-10 days the package will start running and never stop (thus preventing further executions). If I stop the hung job manually it will begin working perfectly again in the next 5 minute interval. The SSIS package is for moving data from an Oracle database to a MSSQL 2005 database. It has 7 steps: Step 1 calls an Oracle Stored Procedure to prepare the temporary tables inside ORACLE Steps 2-6 process the data from the ORACLE tables to the MSSQL tables ORACLE - MSSQL Step 7 calls an Oracle Stored Procedure to clear the ORACLE temporary tables I suspect that the issue is caused by a communications error between the MSSQL server and the ORACLE server. Both the MSSQL database and Agent/package run on one machine with the ORACLE database running over the network. I have enabled logging of the SQL package and after more than 2GB of log file I have captured the instant where the package stops responding: OnPreValidate,ADV-SRV5,NT AUTHORITY\SYSTEM,CallistaIntegrationToMonashCRM_delta,{F88F6C45-CFA2-4801-A2F2-DDF03D458A48},{3A1FB1E3-B76D-444D-876B-D1FBBB9BA246},6/06/2010 10:15:01 AM,6/06/2010 10:15:01 AM,0,0x,(null) OnPreValidate,ADV-SRV5,NT AUTHORITY\SYSTEM,Address,{c5907799-f918-43da-818a-d4bd7f188367},{3A1FB1E3-B76D-444D-876B-D1FBBB9BA246},6/06/2010 10:15:01 AM,6/06/2010 10:15:01 AM,0,0x,(null) OnInformation,ADV-SRV5,NT AUTHORITY\SYSTEM,Address,{c5907799-f918-43da-818a-d4bd7f188367},{3A1FB1E3-B76D-444D-876B-D1FBBB9BA246},6/06/2010 10:15:01 AM,6/06/2010 10:15:01 AM,1074016266,0x,Validation phase is beginning. OnProgress,ADV-SRV5,NT AUTHORITY\SYSTEM,Address,{c5907799-f918-43da-818a-d4bd7f188367},{3A1FB1E3-B76D-444D-876B-D1FBBB9BA246},6/06/2010 10:15:01 AM,6/06/2010 10:15:01 AM,0,0x,Validating Diagnostic,ADV-SRV5,NT AUTHORITY\SYSTEM,Callista,{cb5d6fe3-3ea4-4453-8e5a-965818021df7},{3A1FB1E3-B76D-444D-876B-D1FBBB9BA246},6/06/2010 10:15:01 AM,6/06/2010 10:15:01 AM,0,0x,ExternalRequest_pre: The object is ready to make the following external request: 'IDataInitialize::GetDataSource'. Diagnostic,ADV-SRV5,NT AUTHORITY\SYSTEM,Callista,{cb5d6fe3-3ea4-4453-8e5a-965818021df7},{3A1FB1E3-B76D-444D-876B-D1FBBB9BA246},6/06/2010 10:15:01 AM,6/06/2010 10:15:01 AM,0,0x,ExternalRequest_post: 'IDataInitialize::GetDataSource succeeded'. The external request has completed. Diagnostic,ADV-SRV5,NT AUTHORITY\SYSTEM,Callista,{cb5d6fe3-3ea4-4453-8e5a-965818021df7},{3A1FB1E3-B76D-444D-876B-D1FBBB9BA246},6/06/2010 10:15:01 AM,6/06/2010 10:15:01 AM,0,0x,ExternalRequest_pre: The object is ready to make the following external request: 'IDBInitialize::Initialize'. These messages show the entire log generated for the failed run, for a successful run the output is typically ~2500 lines. I can see that the package is hanging during the initialize operation on the Callista connection (ORACLE database). I have not been able to work out a way to either fix this issue or have the package die gracefully (an error to the log would be A-OK with me). Any help or advice would be greatly appreciated.

Read the article

Linq to Sql get SqlCommand when stored procedure execution fails

- by Tim Mahy

Hi all, currently I'm assigning a TextWriter to the Log property of my Linq to Sql data context (per request instancing) and write this to my logging when an exception is thrown while executing a stored procedure (is strongly typed mapped in the context, so not executing a custom command) however when using ADO.NET we normally inspect the SqlCommand upon unhandled exception to read out the parameters and log them is it possible to access the SqlCommand that was used for executing a Stored Procedure in L2S so we can reuse that existing logging component? This would be far nicer than the current Log TextWriter solution.... greetings, Tim

Read the article

gksudo waits for a few seconds after execution

- by phoenix

i'm frequently using application launchers to run personal bash scripts and thus i often use gksudo in case i do administrative tasks. the problem is that when i execute a command with gksudo,the execution is successful, but afterwards gksudo waits for about 5 seconds before it closes/finishes. in some scripts i use gksudo multiple times, resulting in execution times of a few minutes, even though everything should be done in a few seconds. can anyone help me here? ps: here are my main /etc/sudoers-settings (might have something to do with my problem): Defaults env_reset,!tty_tickets,timestamp_timeout=2 phoenix ALL= NOPASSWD: /bin/mount,/bin/umount,/usr/sbin/firestarter,/usr/bin/truecrypt,/usr/bin/apt-get

Read the article

Same SELECT used in an INSERT has different execution plan

- by amacias

A customer complained that a query and its INSERT counterpart had different execution plans, and of course, the INSERT was slower. First lets look at the SELECT : SELECT ua_tr_rundatetime, ua_ch_treatmentcode, ua_tr_treatmentcode, ua_ch_cellid, ua_tr_cellid FROM (SELECT DISTINCT CH.treatmentcode AS UA_CH_TREATMENTCODE, CH.cellid AS UA_CH_CELLID FROM CH, DL WHERE CH.contactdatetime > SYSDATE - 5 AND CH.treatmentcode = DL.treatmentcode) CH_CELLS, (SELECT DISTINCT T.treatmentcode AS UA_TR_TREATMENTCODE, T.cellid AS UA_TR_CELLID, T.rundatetime AS UA_TR_RUNDATETIME FROM T, DL WHERE T.treatmentcode = DL.treatmentcode) TRT_CELLS WHERE CH_CELLS.ua_ch_treatmentcode(+) = TRT_CELLS.ua_tr_treatmentcode; The query has 2 DISTINCT subqueries. The execution plan shows one with DISTICT Placement transformation applied and not the other. The view in Step 5 has the prefix VW_DTP which means DISTINCT Placement. -------------------------------------------------------------------- | Id | Operation | Name | Cost (%CPU) -------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 272K(100) |* 1 | HASH JOIN OUTER | | 272K (1) | 2 | VIEW | | 4408 (1) | 3 | HASH UNIQUE | | 4408 (1) |* 4 | HASH JOIN | | 4407 (1) | 5 | VIEW | VW_DTP_48BAF62C | 1660 (2) | 6 | HASH UNIQUE | | 1660 (2) | 7 | TABLE ACCESS FULL | DL | 1644 (1) | 8 | TABLE ACCESS FULL | T | 2744 (1) | 9 | VIEW | | 267K (1) | 10 | HASH UNIQUE | | 267K (1) |* 11 | HASH JOIN | | 267K (1) | 12 | PARTITION RANGE ITERATOR| | 266K (1) |* 13 | TABLE ACCESS FULL | CH | 266K (1) | 14 | TABLE ACCESS FULL | DL | 1644 (1) -------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 2 - SEL$AF418D5F / TRT_CELLS@SEL$1 3 - SEL$AF418D5F 5 - SEL$F6AECEDE / VW_DTP_48BAF62C@SEL$48BAF62C 6 - SEL$F6AECEDE 7 - SEL$F6AECEDE / DL@SEL$3 8 - SEL$AF418D5F / T@SEL$3 9 - SEL$2 / CH_CELLS@SEL$1 10 - SEL$2 13 - SEL$2 / CH@SEL$2 14 - SEL$2 / DL@SEL$2 Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("CH_CELLS"."UA_CH_TREATMENTCODE"="TRT_CELLS"."UA_TR_TREATMENTCODE") 4 - access("T"."TREATMENTCODE"="ITEM_1") 11 - access("CH"."TREATMENTCODE"="DL"."TREATMENTCODE") 13 - filter("CH"."CONTACTDATETIME">SYSDATE@!-5) The outline shows PLACE_DISTINCT(@"SEL$3" "DL"@"SEL$3") indicating that the QB3 is the one that got the transformation. Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('11.2.0.3') DB_VERSION('11.2.0.3') ALL_ROWS OUTLINE_LEAF(@"SEL$2") OUTLINE_LEAF(@"SEL$F6AECEDE") OUTLINE_LEAF(@"SEL$AF418D5F") PLACE_DISTINCT(@"SEL$3" "DL"@"SEL$3") OUTLINE_LEAF(@"SEL$1") OUTLINE(@"SEL$48BAF62C") OUTLINE(@"SEL$3") NO_ACCESS(@"SEL$1" "TRT_CELLS"@"SEL$1") NO_ACCESS(@"SEL$1" "CH_CELLS"@"SEL$1") LEADING(@"SEL$1" "TRT_CELLS"@"SEL$1" "CH_CELLS"@"SEL$1") USE_HASH(@"SEL$1" "CH_CELLS"@"SEL$1") FULL(@"SEL$2" "CH"@"SEL$2") FULL(@"SEL$2" "DL"@"SEL$2") LEADING(@"SEL$2" "CH"@"SEL$2" "DL"@"SEL$2") USE_HASH(@"SEL$2" "DL"@"SEL$2") USE_HASH_AGGREGATION(@"SEL$2") NO_ACCESS(@"SEL$AF418D5F" "VW_DTP_48BAF62C"@"SEL$48BAF62C") FULL(@"SEL$AF418D5F" "T"@"SEL$3") LEADING(@"SEL$AF418D5F" "VW_DTP_48BAF62C"@"SEL$48BAF62C" "T"@"SEL$3") USE_HASH(@"SEL$AF418D5F" "T"@"SEL$3") USE_HASH_AGGREGATION(@"SEL$AF418D5F") FULL(@"SEL$F6AECEDE" "DL"@"SEL$3") USE_HASH_AGGREGATION(@"SEL$F6AECEDE") END_OUTLINE_DATA */ The 10053 shows there is a comparative of cost with and without the transformation. This means the transformation belongs to Cost-Based Query Transformations (CBQT). In SEL$3 the optimization of the query block without the transformation is 6659.73 and with the transformation is 4408.41 so the transformation is kept. GBP/DP: Checking validity of GBP/DP for query block SEL$3 (#3) DP: Checking validity of distinct placement for query block SEL$3 (#3) DP: Using search type: linear DP: Considering distinct placement on query block SEL$3 (#3) DP: Starting iteration 1, state space = (5) : (0) DP: Original query DP: Costing query block. DP: Updated best state, Cost = 6659.73 DP: Starting iteration 2, state space = (5) : (1) DP: Using DP transformation in this iteration. DP: Transformed query DP: Costing query block. DP: Updated best state, Cost = 4408.41 DP: Doing DP on the original QB. DP: Doing DP on the preserved QB. In SEL$2 the cost without the transformation is less than with it so it is not kept. GBP/DP: Checking validity of GBP/DP for query block SEL$2 (#2) DP: Checking validity of distinct placement for query block SEL$2 (#2) DP: Using search type: linear DP: Considering distinct placement on query block SEL$2 (#2) DP: Starting iteration 1, state space = (3) : (0) DP: Original query DP: Costing query block. DP: Updated best state, Cost = 267936.93 DP: Starting iteration 2, state space = (3) : (1) DP: Using DP transformation in this iteration. DP: Transformed query DP: Costing query block. DP: Not update best state, Cost = 267951.66 To the same query an INSERT INTO is added and the result is a very different execution plan. INSERT INTO cc (ua_tr_rundatetime, ua_ch_treatmentcode, ua_tr_treatmentcode, ua_ch_cellid, ua_tr_cellid)SELECT ua_tr_rundatetime, ua_ch_treatmentcode, ua_tr_treatmentcode, ua_ch_cellid, ua_tr_cellidFROM (SELECT DISTINCT CH.treatmentcode AS UA_CH_TREATMENTCODE, CH.cellid AS UA_CH_CELLID FROM CH, DL WHERE CH.contactdatetime > SYSDATE - 5 AND CH.treatmentcode = DL.treatmentcode) CH_CELLS, (SELECT DISTINCT T.treatmentcode AS UA_TR_TREATMENTCODE, T.cellid AS UA_TR_CELLID, T.rundatetime AS UA_TR_RUNDATETIME FROM T, DL WHERE T.treatmentcode = DL.treatmentcode) TRT_CELLSWHERE CH_CELLS.ua_ch_treatmentcode(+) = TRT_CELLS.ua_tr_treatmentcode;----------------------------------------------------------| Id | Operation | Name | Cost (%CPU)----------------------------------------------------------| 0 | INSERT STATEMENT | | 274K(100)| 1 | LOAD TABLE CONVENTIONAL | | |* 2 | HASH JOIN OUTER | | 274K (1)| 3 | VIEW | | 6660 (1)| 4 | SORT UNIQUE | | 6660 (1)|* 5 | HASH JOIN | | 6659 (1)| 6 | TABLE ACCESS FULL | DL | 1644 (1)| 7 | TABLE ACCESS FULL | T | 2744 (1)| 8 | VIEW | | 267K (1)| 9 | SORT UNIQUE | | 267K (1)|* 10 | HASH JOIN | | 267K (1)| 11 | PARTITION RANGE ITERATOR| | 266K (1)|* 12 | TABLE ACCESS FULL | CH | 266K (1)| 13 | TABLE ACCESS FULL | DL | 1644 (1)----------------------------------------------------------Query Block Name / Object Alias (identified by operation id):------------------------------------------------------------- 1 - SEL$1 3 - SEL$3 / TRT_CELLS@SEL$1 4 - SEL$3 6 - SEL$3 / DL@SEL$3 7 - SEL$3 / T@SEL$3 8 - SEL$2 / CH_CELLS@SEL$1 9 - SEL$2 12 - SEL$2 / CH@SEL$2 13 - SEL$2 / DL@SEL$2Predicate Information (identified by operation id):--------------------------------------------------- 2 - access("CH_CELLS"."UA_CH_TREATMENTCODE"="TRT_CELLS"."UA_TR_TREATMENTCODE") 5 - access("T"."TREATMENTCODE"="DL"."TREATMENTCODE") 10 - access("CH"."TREATMENTCODE"="DL"."TREATMENTCODE") 12 - filter("CH"."CONTACTDATETIME">SYSDATE@!-5)Outline Data------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('11.2.0.3') DB_VERSION('11.2.0.3') ALL_ROWS OUTLINE_LEAF(@"SEL$2") OUTLINE_LEAF(@"SEL$3") OUTLINE_LEAF(@"SEL$1") OUTLINE_LEAF(@"INS$1") FULL(@"INS$1" "CC"@"INS$1") NO_ACCESS(@"SEL$1" "TRT_CELLS"@"SEL$1") NO_ACCESS(@"SEL$1" "CH_CELLS"@"SEL$1") LEADING(@"SEL$1" "TRT_CELLS"@"SEL$1" "CH_CELLS"@"SEL$1") USE_HASH(@"SEL$1" "CH_CELLS"@"SEL$1") FULL(@"SEL$2" "CH"@"SEL$2") FULL(@"SEL$2" "DL"@"SEL$2") LEADING(@"SEL$2" "CH"@"SEL$2" "DL"@"SEL$2") USE_HASH(@"SEL$2" "DL"@"SEL$2") USE_HASH_AGGREGATION(@"SEL$2") FULL(@"SEL$3" "DL"@"SEL$3") FULL(@"SEL$3" "T"@"SEL$3") LEADING(@"SEL$3" "DL"@"SEL$3" "T"@"SEL$3") USE_HASH(@"SEL$3" "T"@"SEL$3") USE_HASH_AGGREGATION(@"SEL$3") END_OUTLINE_DATA */ There is no DISTINCT Placement view and no hint.The 10053 trace shows a new legend "DP: Bypassed: Not SELECT"implying that this is a transformation that it is possible only for SELECTs. GBP/DP: Checking validity of GBP/DP for query block SEL$3 (#4) DP: Checking validity of distinct placement for query block SEL$3 (#4) DP: Bypassed: Not SELECT. GBP/DP: Checking validity of GBP/DP for query block SEL$2 (#3) DP: Checking validity of distinct placement for query block SEL$2 (#3) DP: Bypassed: Not SELECT. In 12.1 (and hopefully in 11.2.0.4 when released) the restriction on applying CBQT to some DMLs and DDLs (like CTAS) is lifted.This is documented in BugTag Note:10013899.8 Allow CBQT for some DML / DDLAnd interestingly enough, it is possible to have a one-off patch in 11.2.0.3. SQL> select DESCRIPTION,OPTIMIZER_FEATURE_ENABLE,IS_DEFAULT 2 from v$system_fix_control where BUGNO='10013899'; DESCRIPTION ---------------------------------------------------------------- OPTIMIZER_FEATURE_ENABLE IS_DEFAULT ------------------------- ---------- enable some transformations for DDL and DML statements 11.2.0.4 1

Read the article

Internet Explorer : nouvelle vulnerabilité 0-day, les recommandations de Microsoft pour éviter l'exécution de code distant

Nouvelle vulnerabilité 0-day dans Internet Explorer 6,7 et 8 Qui permet l'exécution de code distant Une nouvelle vulnérabilité 0-day dans le navigateur de Microsoft, Internet Explorer vient d'être identifiée. La vulnérabilité pourrait être exploitée par des pirates afin de prendre un contrôle à distance du système vulnérable. La faille se situe au niveau du moteur HTML d'Internet Explorer et peut être exploitée lorsque le navigateur traite des fichiers CSS (Cascading Style Sheets). Avec pour résultat, la possible exécution d'un code arbitraire via une page Web malicieuse. La vulnérabilité touche les versions 7 et 6 d'internet Explorer sur ...

Read the article

CoreDet and Deterministic Multithreaded Execution

A compiler and runtime system for deterministic multithreaded execution Programming - Compiler - Languages - FAQs Help and Tutorials - C

Read the article

How do I return an empty result set from a procedure using T-SQL?

- by Kivin

I'm interested in returning an empty result set from SQL Server stored procedures in certain events. The intended behaviour is that a L2SQL DataContext.SPName().SingleOrDefault() will result in CLR null value. I'm presently using the following solution, but I'm unsure whether it would be considered bad practice, a performance hazard (I could not find one by reading the execution plan), or if there is simply a better way: SELECT * FROM [dbo].[TableName] WHERE 0 = 1; The execution plan is a constant scan with a trivial cost associated with it. The reason I am asking this instead of simply not running any SELECTs is because I'm concerned previous SELECT @scalar or SELECT INTO statements could cause unintended result sets to be served back to L2SQL. Am I worrying over nothing?

Read the article

Migrating SQL Server Databases – The DBA’s Checklist (Part 1)

- by Sadequl Hussain

It is a fact of life: SQL Server databases change homes. They move from one instance to another, from one version to the next, from old servers to new ones. They move around as an organisation’s data grows, applications are enhanced or new versions of the database software are released. If not anything else, servers become old and unreliable and databases eventually need to find a new home. Consider the following scenarios: 1. A new database application is rolled out in a production server from the development or test environment 2. A copy of the production database needs to be installed in a test server for troubleshooting purposes 3. A copy of the development database is regularly refreshed in a test server during the system development life cycle 4. A SQL Server is upgraded to a newer version. This can be an in-place upgrade or a side-by-side migration 5. One or more databases need to be moved between different instances as part of a consolidation strategy. The instances can be running the same or different version of SQL Server 6. A database has to be restored from a backup file provided by a third party application vendor 7. A backup of the database is restored in the same or different instance for disaster recovery 8. A database needs to be migrated within the same instance: a. Files are moved from direct attached storage to storage area network b. The same database is copied under a different name for another application Migrating SQL Server database applications is a complex topic in itself. There are a number of components that can be involved: jobs, DTS or SSIS packages, logins or linked servers are only few pieces of the puzzle. However, in this article we will focus only on the central part of migration: the installation of the database itself. Unless it is an in-place upgrade, typically the database is taken from a source server and installed in a destination instance. Most of the time, a full backup file is used for the rollout. The backup file is either provided to the DBA or the DBA takes the backup and restores it in the target server. Sometimes the database is detached from the source and the files are copied to and attached in the destination. Regardless of the method of copying, moving, refreshing, restoring or upgrading the physical database, there are a number of steps the DBA should follow before and after it has been installed in the destination. It is these post database installation steps we are going to discuss below. Some of these steps apply in almost every scenario described above while some will depend on the type of objects contained within the database. Also, the principles hold regardless of the number of databases involved. Step 1: Make a copy of data and log files when attaching and detaching When detaching and attaching databases, ensure you have made copies of the data and log files if the destination is running a newer version of SQL Server. This is because once attached to a newer version, the database cannot be detached and attached back to an older version. Trying to do so will give you a message like the following: Server: Msg 602, Level 21, State 50, Line 1 Could not find row in sysindexes for database ID 6, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes. Connection Broken If you try to backup the attached database and restore it in the source, it will still fail. Similarly, if you are restoring the database in a newer version, it cannot be backed up or detached and put back in an older version of SQL. Unlike detach and attach method though, you do not lose the backup file or the original database here. When detaching and attaching a database, it is important you keep all the log files available along with the data files. It is possible to attach a database without a log file and SQL Server can be instructed to create a new log file, however this does not work if the database was detached when the primary file group was read-only. You will need all the log files in such cases. Step 2: Change database compatibility level Once the database has been restored or attached to a newer version of SQL Server, change the database compatibility level to reflect the newer version unless there is a compelling reason not to do so. When attaching or restoring from a previous version of SQL, the database retains the older version’s compatibility level. The only time you would want to keep a database with an older compatibility level is when the code within your database is no longer supported by SQL Server. For example, outer joins with *= or the =* operators were still possible in SQL 2000 (with a warning message), but not in SQL 2005 anymore. If your stored procedures or triggers are using this form of join, you would want to keep the database with an older compatibility level. For a list of compatibility issues between older and newer versions of SQL Server databases, refer to the Books Online under the sp_dbcmptlevel topic. Application developers and architects can help you in deciding whether you should change the compatibility level or not. You can always change the compatibility mode from the newest to an older version if necessary. To change the compatibility level, you can either use the database’s property from the SQL Server Management Studio or use the sp_dbcmptlevel stored procedure. Bear in mind that you cannot run the built-in reports for databases from SQL Server Management Studio if you keep the database with an older compatibility level. The following figure shows the error message I received when trying to run the “Disk Usage by Top Tables” report against a database. This database was hosted in a SQL Server 2005 system and still had a compatibility mode 80 (SQL 2000). Continues…

Search Results

Search found 33585 results on 1344 pages for 'sql execution plan'.

Page 149/1344 | < Previous Page | 145 146 147 148 149 150 151 152 153 154 155 156 | Next Page >

- by Paul McLoughlin

- by Sleepless

- by Bill Paetzke

- by BritishDeveloper

- by tim

- by Eric

- by upl8

- by Avinash

- by Techpriester

- by DaveDev

- by RPM1984

- by evilek

- by imanabidi

- by asksuperuser

- by ericsson007

- by Paul Homchick

- by Paul Homchick

- by Adam MacLeod

- by Tim Mahy

- by phoenix

- by amacias

- by Kivin

- by Sadequl Hussain

< Previous Page | 145 146 147 148 149 150 151 152 153 154 155 156 | Next Page >