Search Results

Search found 28930 results on 1158 pages for 'sql ce'.

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

Page 396/1158 | < Previous Page | 392 393 394 395 396 397 398 399 400 401 402 403  | Next Page >

  • Explaining Explain Plan Notes for Auto DOP

    - by jean-pierre.dijcks
    I've recently gotten some questions around "why do I not see a parallel plan" while Auto DOP is on (I think)...? It is probably worthwhile to quickly go over some of the ways to find out what Auto DOP was thinking. In general, there is no need to go tracing sessions and look under the hood. The thing to start with is to do an explain plan on your statement and to look at the parameter settings on the system. Parameter Settings to Look At First and foremost, make sure that parallel_degree_policy = AUTO. If you have that parameter set to LIMITED you will not have queuing and we will only do the auto magic if your objects are set to default parallel (so no degree specified). Next you want to look at the value of parallel_degree_limit. It is typically set to CPU, which in default settings equates to the Default DOP of the system. If you are testing Auto DOP itself and the impact it has on performance you may want to leave it at this CPU setting. If you are running concurrent statements you may want to give this some more thoughts. See here for more information. In general, do stick with either CPU or with a specific number. For now avoid the IO setting as I've seen some mixed results with that... In 11.2.0.2 you should also check that IO Calibrate has been run. Best to simply do a: SQL> select * from V$IO_CALIBRATION_STATUS; STATUS        CALIBRATION_TIME ------------- ---------------------------------------------------------------- READY         04-JAN-11 10.04.13.104 AM You should see that your IO Calibrate is READY and therefore Auto DOP is ready. In any case, if you did not run the IO Calibrate step you will get the following note in the explain plan: Note -----    - automatic DOP: skipped because of IO calibrate statistics are missing One more note on calibrate_io, if you do not have asynchronous IO enabled you will see:  ERROR at line 1: ORA-56708: Could not find any datafiles with asynchronous i/o capability ORA-06512: at "SYS.DBMS_RMIN", line 463 ORA-06512: at "SYS.DBMS_RESOURCE_MANAGER", line 1296 ORA-06512: at line 7 While this is changed in some fixes to the calibrate procedure, you should really consider switching asynchronous IO on for your data warehouse. Explain Plan Explanation To see the notes that are shown and explained here (and the above little snippet ) you can use a simple explain plan mechanism. There should  be no need to add +parallel etc. explain plan for <statement> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY()); Auto DOP The note structure displaying why Auto DOP did not work (with the exception noted above on IO Calibrate) is like this: Automatic degree of parallelism is disabled: <reason> These are the reason codes: Parameter -  parallel_degree_policy = manual which will not allow Auto DOP to kick in  Hint - One of the following hints are used NOPARALLEL, PARALLEL(1), PARALLEL(MANUAL) Outline - A SQL outline of an older version (before 11.2) is used SQL property restriction - The statement type does not allow for parallel processing Rule-based mode - Instead of the Cost Based Optimizer the system is using the RBO Recursive SQL statement - The statement type does not allow for parallel processing pq disabled/pdml disabled/pddl disabled - For some reason (alter session?) parallelism is disabled Limited mode but no parallel objects referenced - your parallel_degree_policy = LIMITED and no objects in the statement are decorated with the default PARALLEL degree. In most cases all objects have a specific degree in which case Auto DOP will honor that degree. Parallel Degree Limited When Auto DOP does it works you may see the cap you imposed with parallel_degree_limit showing up in the note section of the explain plan: Note -----    - automatic DOP: Computed Degree of Parallelism is 16 because of degree limit This is an obvious indication that your are being capped for this statement. There is one quite interesting one that happens when you are being capped at DOP = 1. First of you get a serial plan and the note changes slightly in that it does not indicate it is being capped (we hope to update the note at some point in time to be more specific). It right now looks like this: Note -----    - automatic DOP: Computed Degree of Parallelism is 1 Dynamic Sampling With 11.2.0.2 you will start seeing another interesting change in parallel plans, and since we are talking about the note section here, I figured we throw this in for good measure. If we deem the parallel (!) statement complex enough, we will enact dynamic sampling on your query. This happens as long as you did not change the default for dynamic sampling on the system. The note looks like this: Note ----- - dynamic sampling used for this statement (level=5)

    Read the article

  • SQL Prompt Easter Egg

    - by Johnm
    Having Red Gate's SQL Prompt installed with SQL Server Management Studio has saved me many headaches over the years of its use. It is extremely nice to type in a table name and see not only the column names, but also their data types and identification of primary keys. Another cool feature is the built-in short cut scripts that are included toward the bottom of the suggestion box. An example of these short cut scripts would be to type in the letters  cv and then hit enter and the following template for CREATE VIEW will appear: CREATE VIEW --WITH ENCRYPTION, SCHEMABINDING, VIEW_METADATA AS     SELECT /* query specification */ -- WITH CHECK OPTION GO These scripts are great, and on occasion rather humorous. Recently, I was writing an UPDATE statement that would update a derived and aliased set of data in . An example of such a statement is as follows: UPDATE y SET a.[FieldA] = b.[FieldB] FROM     (         SELECT             a.[FieldA]             ,b.[FieldB]         FROM             [MyTableA] a             INNER JOIN [MyTableB] b                 ON a.[PKA] = b.[PKB]     ) y; Upon typing the UPDATE y portion I hit enter and the expression "A A A A R G H !" appeared resulting in an unexpected burst of laughter. With a dash of curiosity and a pinch of research I discovered that at the bottom of the SQL Prompt suggestion box resides a short cut script called "yell", which is described as "Vent your frustration". Another humorous short cut script is "neo", which is described as "-- I know Kung-Fu". All is required for these to activate is to type the first letter and hit enter. I wonder if there are any undocumented ones?

    Read the article

  • Using SMO to drop a SQL Database

    - by ybbest
    SQL Server Management Objects(SMO) is the API you can use to manipulate the sql server,like create databse and delete database. To get more details you can check the msdn documentation. There are 2 ways you can drop a database 1. You could create a Database object and call Drop method: Dim database As Database = New Database(Your database name) database.Drop() 2.However if you have existing connections to the database ,attempting to drop it using the above method will fail.Recall that when you try to drop the database from management studio ,you can tick the check box to close all the connections before drop the database.It is not so obvious , but you can do the exact same thing using SMO: Dim server As Server= New Server(ServerConn) server.KillAllProcesses(Your database name) server.KillDatabase(Your database name)

    Read the article

  • Encrypting your SQL Server Passwords in Powershell

    - by laerte
    A couple of months ago, a friend of mine who is now bewitched by the seemingly supernatural abilities of Powershell (+1 for the team) asked me what, initially, appeared to be a trivial question: "Laerte, I do not have the luxury of being able to work with my SQL servers through Windows Authentication, and I need a way to automatically pass my username and password. How would you suggest I do this?" Given that I knew he, like me, was using the SQLPSX modules (an open source project created by Chad Miller; a fantastic library of reusable functions and PowerShell scripts), I merrily replied, "Simply pass the Username and Password in SQLPSX functions". He rather pointed responded: "My friend, I might as well pass: Username-'Me'-password 'NowEverybodyKnowsMyPassword'" As I do have the pleasure of working with Windows Authentication, I had not really thought this situation though yet (and thank goodness I only revealed my temporary ignorance to a friend, and the embarrassment was minimized). After discussing this puzzle with Chad Miller, he showed me some code for saving passwords on SQL Server Tables, which he had demo'd in his Powershell ETL session at Tampa SQL Saturday (and you can download the scripts from here). The solution seemed to be pretty much ready to go, so I showed it to my Authentication-impoverished friend, only to discover that we were only half-way there: "That's almost what I want, but the details need to be stored in my local txt file, together with the names of the servers that I'll actually use the Powershell scripts on. Something like: Server1,UserName,Password Server2,UserName,Password" I thought about it for just a few milliseconds (Ha! Of course I'm not telling you how long it actually took me, I have to do my own marketing, after all) and the solution was finally ready. First , we have to download Library-StringCripto (with many thanks to Steven Hystad), which is composed of two functions: One for encryption and other for decryption, both of which are used to manage the password. If you want to know more about the library, you can see more details in the help functions. Next, we have to create a txt file with your encrypted passwords:$ServerName = "Server1" $UserName = "Login1" $Password = "Senha1" $PasswordToEncrypt = "YourPassword" $UserNameEncrypt = Write-EncryptedString -inputstring $UserName -Password $PasswordToEncrypt $PasswordEncrypt = Write-EncryptedString -inputstring $Password -Password $PasswordToEncrypt "$($Servername),$($UserNameEncrypt),$($PasswordEncrypt)" | Out-File c:\temp\ServersSecurePassword.txt -Append $ServerName = "Server2" $UserName = "Login2" $Password = "senha2" $PasswordToEncrypt = "YourPassword" $UserNameEncrypt = Write-EncryptedString -inputstring $UserName -Password $PasswordToEncrypt $PasswordEncrypt = Write-EncryptedString -inputstring $Password -Password $PasswordToEncrypt "$($Servername),$($UserNameEncrypt),$($PasswordEncrypt)" | Out-File c:\temp\ ServersSecurePassword.txt -Append .And in the c:\temp\ServersSecurePassword.txt file which we've just created, you will find your Username and Password, all neatly encrypted. Let's take a look at what the txt looks like: .and in case you're wondering, Server names, Usernames and Passwords are all separated by commas. Decryption is actually much more simple:Read-EncryptedString -InputString $EncryptString -password "YourPassword" (Just remember that the Password you're trying to decrypt must be exactly the same as the encrypted phrase.) Finally, just to show you how smooth this solution is, let's say I want to use the Invoke-DBMaint function from SQLPSX to perform a checkdb on a system database: it's just a case of split, decrypt and be happy!Get-Content c:\temp\ServerSecurePassword.txt | foreach { [array] $Split = ($_).split(",") Invoke-DBMaint -server $($Split[0]) -UserName (Read-EncryptedString -InputString $Split[1] -password "YourPassword" ) -Password (Read-EncryptedString -InputString $Split[2] -password "YourPassword" ) -Databases "SYSTEM" -Action "CHECK_DB" -ReportOn c:\Temp } This is why I love Powershell.

    Read the article

  • Why is prefixing column names considered bad practice?

    - by P.Brian.Mackey
    According to a popular SO post is it considered a bad practice to prefix table names. At my company every column is prefixed by a table name. This is difficult for me to read. I'm not sure the reason, but this naming is actually the company standard. I can't stand the naming convention, but I have no documentation to back up my reasoning. All I know is that reading AdventureWorks is much simpler. In this our company DB you will see a table, Person and it might have column name: Person_First_Name or maybe even Person_Person_First_Name (don't ask me why you see person 2x) Why is it considered a bad practice to pre-fix column names? Are underscores considered evil in SQL as well? Note: I own Pro SQL Server 2008 - Relation Database design and implementation. References to that book are welcome.

    Read the article

  • Oracle : SQL Developer Data Modeler 3.0 disponible, l'outil de modélisation s'ouvre au travail collaboratif

    Oracle : SQL Developer Data Modeler 3.0 disponible L'outil de modélisation s'ouvre au travail collaboratif Oracle vient de lancer une nouvelle version majeure de « SQL Developer Data Modeler », son outil gratuit de modélisation des bases de données. Cette version 3.0 acquiert une dimension collaborative et s'ouvre aux systèmes de contrôle de version. Plusieurs collaborateurs peuvent donc désormais contribuer à l'élaboration du même modèle et suivre, en détail, quel contributeur a fait quels changements sur les modélisations. Pour l'instant, seul Subversion est supporté mais Oracle envisage d'intégrer le support d'autres CVS. Cet outil s'intègr...

    Read the article

  • Number of Weeks between 2 Dates in SQL Server and Oracle

    This post gives you queries in Oracle and SQL Server to find number of weeks between 2 given dates Microsoft SQL Server Syntax: SELECT DATEDIFF (ww, '01/01/1753', '12/31/9999'); Oracle Syntax: SELECT floor(              (to_date('12/31/9999','mm/dd/yyyy')               - to_date('01/01/1753','mm/dd/yyyy')              )              / 7) diff FROM DUAL; span.fullpost {display:none;}

    Read the article

  • Number of Weeks between 2 Dates in SQL Server and Oracle

    This post gives you queries in Oracle and SQL Server to find number of weeks between 2 given dates Microsoft SQL Server Syntax: SELECT DATEDIFF (ww, '01/01/1753', '12/31/9999'); Oracle Syntax: SELECT floor(              (to_date('12/31/9999','mm/dd/yyyy')               - to_date('01/01/1753','mm/dd/yyyy')              )              / 7) diff FROM DUAL; span.fullpost {display:none;}

    Read the article

  • Querying Literals in Entity SQL

    Entity SQL might surprise you if you are building query expressions with some non-string types. Ive blogged about this before with the DateTime literal after trying to use a string to represent the date in my query as Im used to with TSQL. Here is a snip from that post: SELECT VALUE BAModel.Contact(c.ContactID,c.FirstName,c.LastName,c.Title,c.AddDate,c.ModifiedDate) FROM dbo.Contact as c WHERE c.AddDate>="1/1/2007" I was trying to emulate T-SQL here but I need...Did you know that DotNetSlackers also publishes .net articles written by top known .net Authors? We already have over 80 articles in several categories including Silverlight. Take a look: here.

    Read the article

  • SQL Excursions

    - by Grant Fritchey
    Not everyone likes boats. Some people like hanging out, on dry land, maybe sipping a good wine. Interested in doing that AND learning some new stuff about SQL Server? Then you might want to check out SQL Excursions. Denny Cherry (blog|twitter), MVP and terribly sharp individual, has organized this new venture to supply small group training in combination with travel to interesting, and let's face it, comfortable, places. The first trip is already set for Napa Valley. The training will be by Denny and MVP, author, and all round great guy, Thomas LaRock (blog|twitter). Seats for this unique event are going fast. I'd suggest signing up soon. Oh, and did I forget to mention that Red Gate is sponsoring dinner at a fine restaurant? Well, consider it mentioned. Seriously, nice wine, great training, beautiful scenery, networking, all in one place. What are you waiting for?

    Read the article

  • Simultaneously execute two queries on a shell script

    - by Alex
    I have a shell script in which I have to execute two queries (on different databases), spool their results to text files and finally call a c++ program that processes the information on those text files. Something like this: sqlplus user1/pw1@database1 @query1.sql sqlplus user2/pw2@database2 @query2.sql ./process_db_output Both queries take some time to execute. One of them can take up to 10 minutes, while the other one is usually faster. What I want to do is execute them simultaneously and when both are done, call the processing utility. Any suggestion on how to do so?

    Read the article

  • SQL Profiler Through StreamInsight Sample Solution

    In this postI show how you can use StreamInsight to take events coming from SQL Server Profiler in real-time and do some analytics whilst the data is in flight.  Here is the solution for that post.  The download contains Project that reads events from a previously recorded trace file Project that starts a trace and captures events in real-time from a custom trace definition file (Included) It is a very simple solution and could be extended.  Whilst this example traces against SQL Server it would be trivial to change this so it profiles events in Analysis Services.       Enjoy.

    Read the article

  • New Gencode Sql to Linq

    Hi all members I have a programme generator code for c# It gen by 3tier and include MS SQL to Linq, MS SQL, and Access. You can use it and give idia to I can do it better. Links down is below:   http://depositfiles.com/files/38hcd9xf8 ho?c http://www.easy-share.com/1910377507/Ge ... artent.rarThanks you for using it!   record include example and Guide you can down in page dofactory.com or Links below: http://www.easy-share.com/1910377763/Guide Do Patterns In Action 3.5.pdf http://depo...Did you know that DotNetSlackers also publishes .net articles written by top known .net Authors? We already have over 80 articles in several categories including Silverlight. Take a look: here.

    Read the article

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

< Previous Page | 392 393 394 395 396 397 398 399 400 401 402 403  | Next Page >