hi all,
if i want to join two tables and populate data into an oldb destination which option will be better
from performance point of view?
1. using look up transformation
2.writing join query in oldb source using sql commmand option.
Hi all,
first of all I shoud mention it that I have posted a similar question in Oracle forum and recieve enough oracle-related feedback and solution, here (http://www.dbforums.com/showthread.php?t=1609238).
Now I want to know if LIKE searches are awful (performance wise) in SQL Server too, do you recommand any solution to enhance it by some indexing twick? I want to avoid using MS index service (if I spell it correctly) as far as I could ;)
Note: We have a lot of LIKE '%abc%' queries at this system.
-Thanks in advance for your time and help :)
Hello folks,
The other day I was checking the dboption and I couldnt see the "Truncate log on Check Point" option in the dboption window. Wondering if i'm looking at the right place.
Appreciated, If any one point me where can I look for this.
Thanks,
Sindi
Hi,
Is there anyway to create view with read only option as in Oracle ? (OR) is there any
workaround for the same ?
Please advice,
Thanks,
Sam
I was wondering if someone could help explaining how the SQL Server handles all incoming connections, I and a friend started a discussion about using the connection pool in ADO.NET or not in a specific case.
Usually when creating like an ASP.NET Page it's recommended to use the connection pool, because it optimizes the performance, by using already created connection, so we don't have to recreate the connection all the time. I have always thought that the connection in the connection pool had an open connection on the server. So 5 connections in the connection pool, would be 5 open connections at the server. But after having the discussion with my friend I am not so sure anymore.
Say that I would create a client application (.NET using ADO.NET) that connects to the database and work with that data. If I then have 1.000 clients and each client have a connection pool with 5 connections in it (I think that the default numbers for the connection pool), then there would be like 5.000 open connections on the SQL Server, where most of the connections actually never do anything more than hanging around and waiting... And then the connectionpool is not 1 per client but 1 per connectionstring.
So if my client scenario would access data from 2 different databases, there would actually be 10.000 open connections at the SQL Server. So now I think that there must be a server connection pool or something to handle the connection from the clients. So that there would only be like 10-50 open connections at the server for the 1.000 Clients that was connected.
How is it? Is there 1 open connection in the server for every connection in the connection pool? If that's the case, it would be better for the SQL Server if I don't not use the connection pool in the client, but instead open the connection when we need it, and then close it, and taking that little performance hit every time, to help the performance on the SQL Server.
Best regards
- Magnus
I'm pretty new to MSSQL. We have SQL 2K and I was wondering if anyone has seen this issue before.
When I create/modify an Alert there is no option for "SQL Server Performance Condition Alert" in the Type under General tab.
Has anyone seen this before? What can I do to get this type of Alerts?
Just a note -- on my other SQL servers they have the options.
thanks in advance !
We have created a chart in SharePoint 2013 with performance point dashboard designer. We want to change Title of chart. For more information please check attached image.
View 2 Replies View RelatedHi,
I have few views in SQL Server 2005. In Design View, the results of View are ok. In OPEN VIEW option, records are not sorted correctly, ORDER BY is ignored.
What could be the reason for this ?
Thanks a lot in advance!
Which sort of data (from application architecture) point of view it's worth to put in XML datatype (MS SQL 2005)?
View 2 Replies View RelatedI am facing sever Refresh issue in PPS Reports. I have Two Dashboard
Dashboard_One and Dashboard_Two
I have few Filters on both the Dashboard .
In Dashboard_One I have 2 Filters
1)Year filter where Year 2012 is my Default value
2)City Filter where "CityOne" is Default Filter Value
If I select Year"2010" in Period Filter and "CityTwo" In City Filter.I see Related reports . Now I navigate to Dashboard_Two to See Other Reports where I have few Other Filter Like "Country" where I select "CountryThree" . When I navigate Back to Dashboard_one I do not see Dashboard with Default value given to them
I still see Filter value Year=2010 and CountryFilter="CountryTwo" in Dashboard Dashboard_One .. where as I should have seen it based on the Default value given to the Filter.. How should I resolve this refresh issue which I am facing in PPS Dashboard. I do not see Default value in the Filter ,It always give the filter value which was selected later when Navigated back.
Hello all,
First off, I appreciate the time that those of you reading and responding to this request are offering. My quesiton is a theoretical and hopefully simple one, and yet I have been unable to find an answer to it on other searches or sources.
Here's the situation. I am working with SQL Server 2005 on a Windows Server 2003 machine. I have a series of databases, all of which are in Full recovery mode, using a backup device for the full database backups and a separate device for the log backups. The full backups are run every four days during non-business hours. The log backups are run every half hour.
Last week, one of my coworkers found that some rarely-used data was unavailable, and wanted to restore a database to a point in time where the data was available. He told me that point in time was some time back in November.
To accomplish this, I restored the database (in a separate database, as to not overwrite my production database) using the Point in Time Recovery option. I selected November from the "To a point in time" window (I should note that this window is always grey, never white like most active windows, it seems), and the full database backup and the subsequent logs all became available in the "Select the backup sets to restore" window.
I then tried a bevy of different options from the "Options" screen. However, every restore succeeds (ie: it doesn't error out), but seems to be bringing the database back to a current point in time. It's never actually going back to the point in time I specify.
My questions are as follows:
a) Is it possible to do a point in time recovery to a point in time BEFORE the last full database backup?
b) If so, what options would you recommend I use? (ie: "Overwrite the existing database", restore with recovery, etc etc).
I again appreciate any and all advice I receive, and I look forward to hearing from anyone and everyone on this topic. Thank you.
Ryan
Hi,
I have a select statement (that joins 5 tables) and performs fine (when I execute with an
additional 'and' statement that uses the 'like' keyword.)
select f1, f2, f3...f10
from table1, table2, .. ,table5
where table1.f3= table4.f4
and ...
...
...
and table2.f7 = table1.f4
and table4.f4 like 'MYNAME%'
When I create a view with all of the statement except the last:
create view myview as
select f1, f2, f3...f10
from table1, table2, .. ,table5
where table1.f3= table4.f4
and ...
...
...
and table2.f7 = table1.f4
and then when I say
select * from myview where f4 like 'MYNAME%'
the performance tanks.
The query plan completely changes and the resoponse time slows down 100+ times.
I know views are bad, but I am using PeopleSoft and I need to make this view work.
Any ideas how?
Thanks in advance,
Mike Matthews.
Hey there,
I have a view on 5 tables.
The first [tblOrder] 62000 records
The second joined on the first [tblOrderRegel] 2140000 records
The third joined on the second [tblAssortimentSamenstelling] 5800 records
The fourth [tblArtikelVerpakking] 1650 records
And the last also joined on the third [tblAssortiment] 30 records
When I use the next query
SELECT *
FROM [dbBIBSMonitor].[dbo].[vwOrderRegel_1]
WHERE IDOrder in(SELECT IDOrder FROM [vwOrder] WHERE IDRelatie = 6)
GO
It will take more than 60 seconds before the resuld is there
The sub select 'SELECT IDOrder FROM [vwOrder] WHERE IDRelatie = 6' returns a list of ID's. When I make the same select with this ID's instead of the sub select the result returns in 1 second
SELECT *
FROM [dbBIBSMonitor].[dbo].[vwOrderRegel_1]
WHERE IDOrder IN( 4296, 4374, 4433, 9261, 9714, 9732, 9876, 10182, 10223, 17677, 18117, 18393, 28116, 28125, 28246, 31263, 32384, 33482, 33605, 33721, 33792, 36838, 38000, 40579, 40920, 50205, 52292, 52884)
GO
In the first select I have on all tables en index SCAN on the primairy Index with Hash Match/Inner Join to match.
In the second select I have on all tables en index SEEK on the primairy Index with Nested loop/Inner Join to match.
For the sub select 'SELECT IDOrder FROM [vwOrder] WHERE IDRelatie = 6' is not the primairy key used but an other Unique index Containing three columns.
[IDOrder] (also the primairy Key),
[IDStatus] (1, 2 or 3. 90% = 3),
and a date field.
In the view on the subselect is an UNION of three selects.
SELECT IDOrder, Date_1
From tblOrder
WHERE IDStatus = 1
UNION
SELECT IDOrder, Date_2
From tblOrder
WHERE IDStatus = 2
UNION
SELECT IDOrder, Date_2
From tblOrder
WHERE IDStatus = 3
Thats the reason he usses the Unique Index
Is using this index the reason that he does not SEEK on the first table (also tblOrder) of the view in my select???
In other words: Does SQL-server can use only 1 index on a table for a SEEK?
I hope somebody can give me an answer on this complex story.
Thanks Jaap
By the way. I have already tried selects with a INNNER JOIN in stead of a sub query and also a corralated sub query.
Hi all,SQL Server 2000 service pack 3 running on Quad zeon windows 2003 serverwith 6GB of ram.A few months ago we split our databases into operational and completeddata...So now instead of doing select * from tblTest, we do select * fromtblTestboth where tblTestboth is a view like this:Select *From Operational.dbo.tbltestUNIONSelect *From Archive.dbo.tbltestThe tables on both databases are heavily indexed but it still takes muchlonger to do the select * from tblTestBoth than it is to doSelect *From Operational.dbo.tbltestUNIONSelect *From Archive.dbo.tbltestCan anyone please tell me why that is? We are starting to get timeoutsregularly...Should I use indexed views even though the data is inserted/updatedmultiple hundres of times per second?Thank you very much in advance for your help...Regards,Auday*** Sent via Developersdex http://www.developersdex.com ***
View 3 Replies View RelatedHi,
I created a view on two huge tables. I tried to run a simple SELECT statement on this view and it took me several hours to obtain the result. How can I improve the performance of a view? The view should make use of the indexes built in both table, am I right? Thanks.
Hello,
I have the same database on two different servers. One for production and one for testing. A view that I use runs in less than 2 seconds on the test system, but takes almost 2 minutes on the production server.
What I have noticed is on the test server the view will use an index. The production server ends up scanning a whole table. All indices are the same on both machines for the tables involved and I have updated the statistics. I even went through the process of creating a new table with its indices for the table that is being scanned. Both machines have had service pack 1 installed on them.
Any ideas?
I have a view that has been working fine. The data is returned with a second or 2. But in the last 2 or 3 days it will periodically take so long to execute that it will timeout.
I am fairly new to SQL Server 2005, so I am not sure what to check. I am opening the view in Access using an ADP and also in Management Studio.
Also, there are only a couple of users and at the time we are experiencing problems, there really isn't anything going on. No one is trying to run a large query or anything like that.
"SSIS 2012 Catalog doesn't have option to give read access to SSIS Catalog to view package run reports" ... Any luck allowing power developers / operators access to READ the SQL 2012 SSIS Execution Reports without granting them SSIS_Admin or Sysadmin?
According to this link posted back in 2011 (w/ Microsoft's feedback in Nov 2011: "We’re closing this issue as “Won’t Fix.” At this point the bug does not meet our bar for resolving prior to SQL Server 2012 RTM. As we approach the SQL Server 2012 release the bar for making code changes gets progressively higher." URL....Regarding Permissions to SSIS Catalog, here are the findings. We can give access in three ways:
1. READ Access – We can provide a user db_datareader access. With this the user can see the objects within the SSIS catalog database, but cannot see the reports.
2. SSIS_ADMIN – Add the user to this database role in SSISDB. With this the user can view the reports. But it also provides them privileges to modify catalog information which is not expected. We can add it using below script EXEC sp_addrolemember 'ssis_admin' , 'REDMONDPAIntelAnalyst'
3. SYSADMIN - Add the user to this server role. This will make the user an admin on the SQL server. This is not intended. Is there any method available which will have provision to give read only access to see SSIS Catalog package execution reports and not having modify Catalog access.
I have a complex(long) SQL statement inside of a stored procedure which feeds several variables from 2 tables. Something like
Select @var1, @var2, etc from
table1, table2 where
table1.id = table2.id
Is there any advantage to creating a view for this statement
and selecting from that, even though this resides in a stored procedure?
I have an Indexed View with followed syntax:
CREATE VIEW VW_Emplacamentos
WITH SCHEMABINDING
AS
SELECTA.CD_Linha -- This is the unique clustered field
A.DT_Emplacamento,
A.NR_CNPJ,
A.CD_Municipio,
A.VL_Potencia,
A.VL_CapacidadeCarga,
A.NR_QtdPax,
A.NR_AnoFabricacao,
A.NR_Chassi,
A.SG_UF,
A.CD_MarcaModelo,
A.CD_Procedencia,
A.CD_Combustivel,
B.NM_Municipio,
C.NM_Combustivel,
D.NM_MarcaModelo,
D.CD_Segmento,
D.CD_Fabricante,
E.NM_Segmento,
F.NM_SubSegmento,
G.NM_Fabricante
FROM dbo.TB_Emplacamentos AS A
INNER JOIN dbo.TB_Municipio AS B ON A.CD_Municipio = B.CD_Municipio
INNER JOIN dbo.TB_Combustivel AS C ON A.CD_Combustivel = C.CD_Combustivel
INNER JOIN dbo.TB_ModeloVeiculo AS D ON A.CD_MarcaModelo = D.CD_MarcaModelo
INNER JOIN dbo.TB_SegmentoVeiculo AS E ON D.CD_Segmento= E.CD_Segmento
INNER JOIN dbo.TB_SubSegmentoVeiculo AS F ON D.CD_SubSegmento = F.CD_SubSegmento
INNER JOIN dbo.TB_Fabricante AS G ON D.CD_Fabricante = G.CD_Fabricante
The following query is not getting perfomance. I don't what's going on.
SELECT COUNT(*)
FROM VW_Emplacamentos
WHERE DT_Emplacamento BETWEEN '20040301' AND '20040316'
AND NM_Fabricante = 'VW'
I've created an compost index on the following sequece
DT_Emplacamento,NM_Fabricante,NM_Segmento
But it still slow.
I really apreciate comments.
I recently added several indexed views to a high traffic table (high volume of both inserts and selects) because I needed to have some complicated unique constraints involving columns that allow NULL.
While I didn't notice performance problems at first, it appears to be that the CPU on the SQL Server is getting pegged when more than 10 or 15 simultaneous inserts are happening on the table. This is a quad proc 3Ghz Xeon, so the fact the CPU is hitting 90%+ while doing 15 inserts a second doesn't make sense to me.
Very quickly the sproc that is being repeatedly called by some middle tier components is taking 30+ seconds to execute, eventually causing timeouts. This sproc is very simple. It does a few quick select statements (that take well under 100ms), and then does an insert into the table in question. That's it.
The only thing I can think of is that the overhead of 3 separate indexed views on the table, each of which contains a significant subset of the total rows in the table (550,000+ rows), is causing SQL Server to get swamped trying to keep the indexes up to date.
Does this seem like a possibility? I'm planning on temporarily removing those indexed views to see how it performs without them, although this is dangerous because it creates the potential for invalid data.
Hi everyone In my SqlServer Management Studio Express, on start up it shows the server type option, but greyed.So that value is fixed to database engine. ( I'm trying to work on an SqlServer Compact Edition database through the SSMStudiothat's why I'm trying to get this to change.)Besides, after I connect i go to the Object Explorer, expand the server node, and go to Replication.When i expand replication, i get the "Local Subscription" option, but nothng for Publication.( I want to work on Merge Replication, that's why I desparately need Publication to work)Am i missing something here? I did not install SqlServer separately, I only have what comes bundled with the Visual Studio 2005 Setup.
View 2 Replies View RelatedGreetings,I have 3 servers all running SQL Server 2000 - 8.00.818. Lets callthem parent, child1, and child 2.On parent, I create a view called item as follows:CREATE view Item asselect * from child1.dbchild1.dbo.Item union allselect * from child2.DBChild2.dbo.ItemOn child1 and child2, I have a table "item" with a column named "id"datatype uniqueidentifier (and many other columns). There is anon-clustered index created over column "id".When I connect to the parent server and select from the viewSelect id, col1, col2, . From item where id =280A33E0-5B61-4194-B242-0E184C46BB59'The query is distributed to the children "correctly" (meaning itexecutes entirely (including the where clause) on the children serverand one row is returned to the parent).However, when I select based on a list of idsSelect id, col1, col2, . From item where id in(280A33E0-5B61-4194-B242-0E184C46BB59',376FA839-B48A-4599-BC67-25C6820FE105')the plan shows that the entire contents of both children item tables(millions of rows each) are pulled from the children to the parent,and THEN the where criteria is applied.Oddly enough, if I put the list of id's I want into a temp tableselect * from #bv1id------------------------------------280A33E0-5B61-4194-B242-0E184C46BB59376FA839-B48A-4599-BC67-25C6820FE105and thenSelect id, col1, col2, . From item where id in (select * from #bv1)the query executes with the where criteria applied on the childrendatabases saving millions of rows being copied back to the parentserver.So, I have a hack that works (using the temp table) for this case, butI really don't understand the root cause. After reading online books,in a way I am confused why ANY of the processing is done on thechildren servers. I quote:================================================Remote Query ExecutionSQL Server attempts to delegate as much of the evaluation of adistributed query to the SQL Command Provider as possible. An SQLquery that accesses only the remote tables stored in the provider'sdata source is extracted from the original distributed query andexecuted against the provider. This reduces the number of rowsreturned from the provider and allows the provider to use its indexesin evaluating the query.Considerations that affect how much of the original distributed querygets delegated to the SQL Command Provider include:The dialect level supported by the SQL Command ProviderSQL Server delegates operations only if they are supported by thespecific dialect level. The dialect levels from highest to lowest are:SQL Server, SQL-92 Entry level, ODBC core, and Jet. The higher thedialect level, the more operations SQL Server can delegate to theprovider.Note The SQL Server dialect level is used when the providercorresponds to a SQL Server linked server.Each dialect level is a superset of the lower levels. Therefore, if anoperation is delegated to a particular level, then Queries involvingthe following are never delegated to a provider and are always it isalso delegated to all higher levels.evaluated locally:bituniqueidentifier================================================This suggests to me that any query having where criteria applied to adatatype uniqueidentifier will have the where criteria applied AFTERdata is returned from the linked server.Any ideas on the root problem, and a better solution to get the queryand all the where criteria applied on the remoted linked server?Thanks,Bernie
View 5 Replies View RelatedI am running A View that INSERTS into #Temp Table - On Only Certain Days the INSERT Speed into #tempDB is so slow.
Attached snapshot that shows after one minute so many few records are inserted - and it dosent happen every day somedays its very fast.
So I started a new job recently and have noticed a few strange configurations. Typically I would never mess with min memory per query option and index create memory option configuration because i just haven't seen any need to. My typical thought is that if it isn't broke... They have been modified on every single server in my environment.
From Books Online:
• This option is an advanced option and should be changed only by an experienced database administrator or certified SQL Server technician.
• The index create memory option is self-configuring and usually works without requiring adjustment. However, if you experience difficulties creating indexes, consider increasing the value of this option from its run value.
I'm running SQL Server 2005 on a Server 2003 machine serving both our home network as well as a remote site through a point-to-point T1. While file transfer speeds are up to par, the remote site's interaction with SQL Server (Point of sale system) is very slow. After testing I am certain that it has nothing to do with the actual physical machine in place neither is it an issue with the program itself since speeds are as they are supposed to be over the home network lan. It seems that there might be a packet size issue or something of the sort. Has anyone dealt with this before or have any thoughts?
Thanks,
Peter
1. Use mssql server agent service to take the schedule
2. Use a .NET windows service with timers to call SqlClientConnection
above, which way would be faster and get a better performance?
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 RelatedI am looking to create a constraint on a table that allows multiplenulls but all non-nulls must be unique.I found the following scripthttp://www.windowsitpro.com/Files/0.../Listing_01.txtthat works fine, but the following lineCREATE UNIQUE CLUSTERED INDEX idx1 ON v_multinulls(a)appears to use indexed views. I have run this on a version of SQLStandard edition and this line works fine. I was of the understandingthat you could only create indexed views on SQL Enterprise Edition?
View 3 Replies View RelatedWrite a CREATE VIEW statement that defines a view named Invoice Basic that returns three columns: VendorName, InvoiceNumber, and InvoiceTotal. Then, write a SELECT statement that returns all of the columns in the view, sorted by VendorName, where the first letter of the vendor name is N, O, or P.
This is what I have so far,
CREATE VIEW InvoiceBasic AS
SELECT VendorName, InvoiceNumber, InvoiceTotal
From Vendors JOIN Invoices
ON Vendors.VendorID = Invoices.VendorID
[code]...
Hi guys 'n gals,
I created a query, which makes use of a temp table, and I need the results to be displayed in a View. Unfortunately, Views do not support temp tables, as far as I know, so I put my code in a stored procedure, with the hope I could call it from a View....
I tried:
CREATE VIEW [qryMyView]
AS
EXEC pr_MyProc
and unfortunately, it does not let this run.
Anybody able to help me out please?
Cheers!
I compared view query plan with query plan if I run the same statementfrom view definition and get different results. View plan is moreexpensive and runs longer. View contains 4 inner joins, statisticsupdated for all tables. Any ideas?
View 10 Replies View RelatedI normally develop for MySQL and PHP, but we have a new application that runs on MS SQL and I'm having difficulty using the same structure as MySQL to run queries. Can someone here point me to a good resource to learn the basics?
Thanks!