We are inserting huge amount of data (could be several millions) during a fact upload. Therefore after the upload we recomputed the statistics. In oracle this can be done using the following sql statement:
analyze table <table_name> compute statistics
Do you know if there is any equivalent statement in SQL*Server? it has to run via JDBC like a SQL statement.
Thanks.
After our upgrade and migration from v6.5 to v7.0, there are new indexes shown on the sysindexes table. All of these new indexes have names that start with the characters '_WA_Sys_'.
Before the upgrade, one database had 88 indexes but now it has 643. One table went from 1 index to 92.
The systems person who did the install and upgrade believes that these new objects only appear to be indexes, and that they are statistics that are automatically generated to aid in performance.
a) Does anyone else have these?
b) What are they?
c) Where can I read about them?
d) Could they cause performance slowdown?
I'm looking into the automatic recompilation of stored procedures andI have been reading up on the "Do not recompute statistics" option onindexes.Am I correct in concluding that disabling the "Do not recomputestatistics" option for an index, will ensure that no automaticrecompilations will occur as a result of updates to data in thatindex?Am I also correct in understanding that the "Update Statistics" willstill update statistics for the index even if the "Do not recomputestatistics" option is disabled?RegardsBjørn
View 1 Replies View RelatedHello group.I have an issue, which has bothered me for a while now:I'm wondering why the column statistics, which SQL Server wants me tocreate, if I turn off auto-created statistics, are so important to theoptimizer?Example: from Northwind (with auto create stats off), I do the following:SELECT * FROM Customers WHERE Country = 'Sweden'My query plan show a clustered index scan, which is expected - no indexexists for Country. BUT, the query plan also shows, that the optimizer ismissing a statistic on Country, which tells me, that the optimizer wouldbenefit from knowing this.I cannot see why? (and I've been trying for a while now).If I create the missing statistics, nothing happens in the query plan (andwhy should it?). I could understand it, if the optimizer suggested an indexon Country - this would make sense, but if creating the missing index, queryanalyzer creates the statistics with an empty index, which seems to me to beless than usable.I've been thinking long and hard about this, but haven't been able to reacha conclusion :) It has some relevance to my work, because allowing theoptimizer to create missing statistics limits my options for designingindexes (e.g. covering) for some rather wide tables, so I'm thinking why notturn it off altogether. But I would like to know the consequences - hopesomebody has already delved into this, and knows a good explanation.RgdsJesper
View 5 Replies View RelatedWhat is the unit of the numbers you get in the Time Statistics-part when running a query in Microsoft SQL Server Management Studio with Client Statistics turned on?
Currently I get mostly 0´s, but if I try and *** up a query on purpose I can get
it up to around 30... Is it milliseconds or som made up number based on clockcycles or... ?
I would also like to know if it´s possible to change the precision.
- Nikolaj
I have read similar posts to this, but I am still having problems.
I am trying to use connection pooling to connect to a local SQL Server 2005 database. I am running my application using
MyEclipse Enterprise Workbench. I have verified that sqljdbc.jar resides in "WebRoot/WEB-INF/lib/"
"WebRoot/WEB-INF/web.xml":
<?xml version="1.0" encoding="UTF-8"?>
<web-app version="2.4" xmlns="http://java.sun.com/xml/ns/j2ee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsichemaLocation="http://java.sun.com/xml/ns/j2ee
http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd">
<resource-ref>
<res-ref-name>jdbc/DefaultDS</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>
</web-app>
"WebRoot/META-INFcontext.xml":
<?xml version="1.0" encoding="UTF-8"?>
<Context>
<Resource name="jdbc/DefaultDS"
auth="Container"
type="javax.sql.DataSource"
username="tec"
password="tec"
driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDrive"
url="jdbcqlserver://localhost:1433/tec;databaseName=tec;user=tec;password=test;"
validationQuery="select 1"
maxActive="10"
maxIdle="2"/>
</Context>
Classpath:
<?xml version="1.0" encoding="UTF-8"?>
<classpath>
<classpathentry kind="src" path="src"/>
<classpathentry kind="con" path="org.eclipse.jdt.launching.JRE_CONTAINER"/>
<classpathentry kind="con" path="com.genuitec.eclipse.j2eedt.core.J2EE14_CONTAINER"/>
<classpathentry kind="lib" path="WebRoot/WEB-INF/lib/dom.jar"/>
<classpathentry kind="lib" path="WebRoot/WEB-INF/lib/jaxen-full.jar"/>
<classpathentry kind="lib" path="WebRoot/WEB-INF/lib/jaxp-api.jar"/>
<classpathentry kind="lib" path="WebRoot/WEB-INF/lib/jdbc2_0-stdext.jar"/>
<classpathentry kind="lib" path="WebRoot/WEB-INF/lib/sqljdbc.jar"/>
<classpathentry kind="lib" path="WebRoot/WEB-INF/lib/jstl.jar"/>
<classpathentry kind="lib" path="WebRoot/WEB-INF/lib/mail.jar"/>
<classpathentry kind="lib" path="WebRoot/WEB-INF/lib/sax.jar"/>
<classpathentry kind="lib" path="WebRoot/WEB-INF/lib/saxpath.jar"/>
<classpathentry kind="lib" path="WebRoot/WEB-INF/lib/standard.jar"/>
<classpathentry kind="lib" path="WebRoot/WEB-INF/lib/xalan.jar"/>
<classpathentry kind="lib" path="WebRoot/WEB-INF/lib/xercesImpl.jar"/>
<classpathentry kind="output" path="WebRoot/WEB-INF/classes"/>
</classpath>
Code to connect:
import java.io.Serializable;
import java.sql.*;
import javax.sql.*;
import javax.naming.*;
public int testConnect(){
Connection conn = null;
InitialContext ctx = null;
java.sql.Statement stmt = null;
try {
ctx = new InitialContext();
Context envCtx = (Context) ctx.lookup("java:comp/env");
DataSource ds = (DataSource) envCtx.lookup("jdbc/DefaultDS");/*This is generating the Cannot load JDBC driver class... error*/
conn = ds.getConnection();
stmt = conn.createStatement();
return CONSTANT.SUCCESS;
} catch (Exception e) {
return CONSTANT.FAILURE;
}finally {
try {
if (stmt != null)
stmt.close();
if (conn != null)
conn.close();
if (ctx != null)
ctx.close();
} catch (Exception ex) {
// do nothing
return CONSTANT.FAILURE;
}
}
}
Any ideas would be greatly appreciated.
Hi all,
We've just stumbled on a 1.0 version incompatibility with the JDBC specs.
Problem: A table with SMALLINT column. According to JDBC specs version 3.0
(p.B-179) and 4.0 (p.221)), the value should be converted to Integer type.
Unfortunatelly we get a Short object :(
Now, I remember, this case was also affecting old JSQLConnect driver from
DataDirect. Could that problem sneak to new MS driver too?
Please let me know any resolution to this problem if exists.
The issue has not been fixed in CTP 1.1 version. Any ideas if it can be fixed??
Cheers,
Piotr
I've got an import app written in Java. One table I'm importing from contains 22 million records. When I run the app in a 2000 environment, I have my max heap set at 512, and the table gets imported. When I run in a 2005 environment, I have to change the max heap to 1152 or it will error out with a similiar error:
com.microsoft.sqlserver.jdbc.SQLServerException: The system is out of memory. Use server side cursors for large result sets:Java heap space. Result set size:854,269,999. JVM total memory size:1,065,484,288. (<--this is with max heap at 1024)
what is the difference between the 2000 and 2005 JDBC that I have to set max heap in one and not the other?
Can I copy statistics in SQL Server from one environment to another without copying the actual data. For example from production to development. It is possible to copy statistics in other databases, like DB2/UDB, Oracle. Reason is to execute some poor performing SQLs and analyze their execution plan.
Did not find anything on this subject in BOL. Since statistics is stored in the statblob column of sysindexes, I tried updating statblob column of the index and rowcnt columns of table & index to mimic the copy. After my updates to 'TO' table
dbcc show_statistics ( stat_test2, stat_test2_idx)
showed the results that is identical to the statistics of my FROM table.
But when I execute a small SELECT on (FROM) table(which contains the original, required stats) and the (TO) table (where the statistics is now copied), I get 2 different execution plans. This means I am not successful in my attempt to cheat the optimizer.
Is there any more columns to be updated?
Is there another direct way to do this?
Thanks in advance.
Gana.
In using ADO to connect to SQL Server, I'm trying to retrieve multiple datasets AND statistics that are usually returned via the OnInfoMessage event. For those that are familiar with SQL Server, I need the results returned by the SET STATISTICS IO ON and SET STATISTICS PROFILE ON options. Anyone had any luck doing this before?
Thanks in advance.
hi all
I am working on query performance and tuning.
I want to save current statistics for the latter use. do SQL Server provide any utility or command (like exec dbms_stats in ORACLE and OPTDIAG in sybase) to do this?
thanks
Gourav
I am working on an existing infrastructure and i do not have liberty to change much right now. I am in a situation where app issues update statistics command quite often. So frequently that sometimes one blocks another. Is there any way i can do something like this
IF ( update_statistics going on)
dont do anything
else
run update statistics
This is temporary solution untill i fix bad inline SQL code (in app) and use SPs.
Getting events in the default trace saying missing column statistics on a column...
1.The column is the primary key column ( identity )
Currently our database size is around 350G. It will grow up to 1.5 TB
We have the
Auto create statistics option :True,
auto update statistics option :True,
auto update statistics asynchronously option : False
at database level
we have a weekly job, update statistics running very long time. It is created through maintenance plan using the option full scan.
Previously they tested with sampling but instead of full scan running with the sampling effected the queries.
Is there option to avoid the long time job duration.
If we didn't run the statistics manually what will happen? How do you maintain statistics with large databases
I have been asked to create a report for one of our clients. The report is pretty basic but I am concerned about the overheads with my planned approach.The report is at a table and field grain to include values for:
* Min column value
* Max column value
* Number of discrete values
* Number of populated values (not NULL)
My current plan is to have a cursor over a limited view of sys.tables and sys.columns that will run a dynamic SQL query to import the results into a table that I can then output.There must be a better way of doing this and I don't have access to any DQS services.
Hi,
I am using SQL Server 2000 sp4 (standard edition); when I run an Update Stats command against a table I get error 3628:
UPDATE STATISTICS IMSV7.BLITEMRT WITH SAMPLE 20 PERCENT
Server: Msg 3628, Level 16, State 1, Line 1
A floating point exception occurred in the user process. Current transaction is canceled.
Does anyone know of a fix or a workaround?
Are there separate jdbc drivers for Sql Server 7 or should I use the 2000 drivers?
View 1 Replies View RelatedDear all,Where can I find JDBC for MS SQL server?Thx for reply.Victoria
View 2 Replies View Related
I want to be able to reproduce my production execution plans on development with copying data.
Hi!
I have developed a database in MS SQL Server 2005 Express, to which I would require only bulkadmin server role from an external java application, because I only need to update rows, insert values or use select queries in the database.
The problem is that, using either the Microsoft JDBC Driver 1.1 or the Java JDBC ODBC Driver and the Windows XP Data Base (ODBC) configurations, I need a user with sysadmin server role inside Sql Server, otherwise JDBC won't connect to the database using the selected user. Even if I leave the sql login with setupadmin or any server role lower than sysadmin, the connection is refused.
Is there no way to connect using JDBC to MS Sql Server 2005 other than granting the connected user sysadmin rights? My code looks as follows:
Code Snippet
String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
String url = "jdbc:sqlserver://FIREBLADE\SQLEXPRESS";
String user = "username";
String password = "password$$";
Connection conn;
Class.forName(driver);
conn = DriverManager.getConnection(url,user,password);
if (conn != null)
System.out.println("SQL Server Connection established ...");
I have heard that Java JDBC connections to Microsoft require high-level access.
Any informed answer is more than welcome. Thanks for reading my post!
please help it's urgent i badly need the jdbc driver for mssql for my project
thank u
Hi at all, i'm new here, your communitiy seems too much preparated.
My question is simple...
i must to connect via jdbc to a ms sql server 2000 database, but i'm in doubt to select which jdbc driver to use. your suggest over microsoft'drivers? i found on the internet the open source drivers jdts...what about them?
thank you and excuse for by bad english! :p
I am having difficulty connecting to a SQL Server 7.0 database. I have an application running on JBoss 3.2.x, and which connects to an Oracle database without problem. However, I also need to have the application connect to a SQL Server 7.0 database.
I have a set of jar files which are added to my classpath for SQL Server. There were three jars (msutil.jar, msbase.jar and mssqlserver.jar) that had to be added to the classpath to get that far. However the application reported that the connection could not be established.
So, I wrote a java class to see if I could replicate the problem, or establish a connection and possibly figure out whether it was the application or the database. The class threw an exception and indicated that the driver was for SQL Server 2000, and was incompatible with SQL Server 7.0.
Has anyone used SQL Server 7.0 with JBoss and can point me in the right direction?
Thanks.
How do I configure the MS-SQLSERVER to accept JDBC connections andprocess the sql?I'm using SQLSERVER 2000 on Windows NT.Thanks for your help
View 2 Replies View RelatedHi
I am using JDK 1.6 . I have downoladed the JDBC driver from
http://www.microsoft.com/downloads/details.aspx?familyid=07287B11-0502-461A-B138-2AA54BFDC03A&displaylang=en
This page mentions certain versions of JDK that this driver works with, but 1.6 is not one of them.
How can I use this driver with JDK 1.6?
Thanks
J.
I can easily find user created stat in a databaseSELECT * FROM DB.sys.stats WHERE user_created=1But how do I determine what tables those stats are in? with over 6000 tables I don't feel like looking through all the tables.
View 2 Replies View RelatedI'm working on databases where statistics of some indexes (tables) are changing too frequently. Once I update them manually, one minute after they get 10-20% change, and five minutes after they get over 100% change. Tables get updated very frequently (multiple times in a second).
When I run a query to read from sys.stats, sys.dm_db_stats_properties and other dynamic views, I see that they were last updated when I did it manually, but the change rate overpassed the 500+20% (tables have multiples of 10K rows). Auto create and update statistics are set to true on all databases, and I don't know why sql server does not do that automatically.
I'm looking for a quick script that someone has already written to update statistics (not to rebuild or re-organise) on specific indices in specific databases - I guess loop though a table comprising of a list of databases and the indices.
I know Ola has one but I'm not look for something that is that complicated. If I cannot find one I'm going to have to write one myself - I want to try and avoid re-inventing the wheel as tomorrow I have to do this work and it's about 7K plus indices in about 10+ databases.
If I rebuild some indexes that are above 30% of average fragmentation, should I after that update statistics?
Also, How can I see if I Need to update statistics^on the tables of my database?
Hi this is my first post
I really needs some help
I have been stressing with issue for last few days.
I have a stored Procedure ramApplicationAddTest, I can run it through SQL Query Analyser without any problem.
I can call this using JDBC and it works, here is how I make call I make to SP
var con1 = DBDriver.getConnection ("alias:ProclaimConnect");
var stmt = con1.prepareCall( "{call ramApplicationAddTest(?,?,?,?,?)}");
stmt.setString(1,'BuildApp');
stmt.setString(2,'BASwimPool');
stmt.setString(3,'Test Test....');
stmt.setString(4,'');
stmt.setFloat(5,0);
stmt.execute();
stmt.close();
con1.close();
I also have another SQL 2000 Stored Procedure called
RamApplicationAddTest1, I can run this SP also through SQL Query Analyser without any problem.
But when I try to run this using JDBC it, gives me following Exception.
SQLState[ERR-Unexpected]ErrorCode[0]Message[java.lang.NullPointerException
at com.hs.form.jdbc.JDBCErrorhandler.isMssqlConnectio nErrorInTx(JDBCErrorHanlder.
An exception 'DatabaseQueryException' has occurred at DatabaseQueryClientWinInetImpl.cpp (291).
var con2 = DBDriver.getConnection ("alias:ProclaimConnect");
var stmt = con2.prepareCall( "{call ramApplicationAddTest1(?,?,?)}");
stmt.setString(1,’BuildApp’);
stmt.setString(2,’BASwimPool’);
stmt.setString(3,’Test’);
stmt.execute();
stmt.close();
con2.close();
I hope somebody can give me some hints to fix this.
Amar
I have SQL server installed on a XP box. I need to access it from other machines using Java code. Where can I download a JDBC driver?
Thanks.
Hello JoeOver the past several years, I have found your responses to jdbcusage/driver related issues to be extremely helpful. I am sure thatyou're very busy so I will make my question as brief as possible - thereason I didn't post this to a newsgroup is that I don't want to hearany more hype or links to benchmarks (since the vast majority of thebenchmarks don't test concurent/mixed-transaction environments). (I amalso posting a modified version of this message to some newsgroups, butbased on past responses - don't have too much hope that these will leadto a resolution.) If the only way to answer this question would be tohave a phone conversation, my company would be more than happy to pay aconsulting-service fee for your time.My situation:My company has a enterprise level web-app that targets SQL Server. Wedon't use J2EE - so we stick to a simple to administer web containers(JRun,Tomcat). Recently, we have been getting worse and worseperformance from our jdbc driver (we use the free MS jdbc driver) -things like strange transaction resource handling, chopy overallperformance, etc.. I have spent significant time tweaking it (followingvarious advice - many times yours - on newgroups... I can go intodetails, but I don't want to take up your time). As the project'sarchitect, I need to do something about this problem - but varioushigh-level tunnings that I have done to the way we use connections withthis driver haven't significantly improved perfomance under normaleveryday load. (Our queries often span tables with millions of records,and are relatively dynamic. We use a seperate pool of autocommit-offconnections for writes, and autocommit-on connecions for reads)So, then, my question - in your expert opinion - what is the BESTdriver from SQL Server 2000 given the following needs:Things I need it to have are solid jdbc 1.0, solid transactionhandling, decent concurrent load handling, stable implementation ofresource handling (i.e. auto closing result sets when parent statementsclose, closing of result sets & statements when connections close) andsupport for multiple open statements/result sets per connection.Things I do not have a direct need for:Connection Pooling (I keep my own pool of open connections), DataSourcesupport, distributed transactions or 2-phase commit support, RowSetvariants, jdbc 3.0 autokey generation, J2EE compliance. We do make useof updatable result sets - but I don't care if it isn't supported sinceI can still use MS driver for those, as they are mainly done it batchjobs).Things that are nice:It would also be good to have a driver that can handle prep statementcaching on a driver level (vs connection level) although this isn't anecessity, and if it is available - it would need to have aconfigurable caching strategy (or have a way to be turned off :)I very much appreciate your help in advanceThanksGary BogachkovSystem ArchitectStericycle Direct Return
View 1 Replies View RelatedDear All,I developed an web application which use MS SQL Server 2000. Iencounter the following SQLException "[Microsoft][SQLServer 2000Driver for JDBC]Error setting up static cursor cache". Did anyoneencounter this problem before? What does this exception mean and howto solve this problem?[Remarks: The web application work properly on development machine andthis machine but encounter this exception when move to anothermachine, so is this exception related to the machine?]Thank you for your attention.Yours faithfully,Benny
View 2 Replies View Related